# The Heavy Lifters > In some categories, a handful of big orders carry the rest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem A merchandising team treats any order of three or more units as a bulk order and wants to find the categories that lean on them. Return the categories where bulk orders account for more than half of total revenue, giving just the category name. ## Worked solution and explanation ### What this problem is really testing Strip away the merchandising story and this is a per-group revenue-share test: for each category, does the revenue from bulk orders clear half of that category's entire revenue. The skill being probed is whether you can compute a conditional numerator and an unconditional denominator in the SAME aggregation pass. Anyone can identify bulk orders. The trap is that if you filter them out with a WHERE clause before grouping, you throw away the non-bulk rows and lose the denominator, so you can no longer ask what SHARE bulk orders represent. Get that wrong and every category with even a single bulk order looks like it qualifies, so Books and Clothing sneak in even though their big orders are a minority of revenue. > **Trick to solving** > > Keep the whole category in the denominator. A conditional SUM gives you the bulk revenue, a plain SUM over the same group gives you the total, and HAVING compares them: SUM(CASE WHEN quantity >= 3 THEN total_amount ELSE 0 END) > 0.5 * SUM(total_amount). One pass, both numbers. --- ### Build it up #### Step 1: Attach category to each transaction Join `transactions` to `products` on `product_id` so every transaction carries its `category` alongside `quantity` and `total_amount`. #### Step 2: Group by category `GROUP BY p.category` collapses all of a category's transactions into one row, which is where both the bulk revenue and the total revenue get computed. #### Step 3: Compare bulk revenue to half the total In HAVING, build the bulk numerator with a conditional SUM over `quantity >= 3` and the denominator with a plain SUM over the same rows, then keep only categories where the numerator beats half the denominator. --- ### The solution **Conditional numerator over unconditional denominator** ```sql SELECT p.category FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY p.category HAVING SUM(CASE WHEN t.quantity >= 3 THEN t.total_amount ELSE 0 END) > 0.5 * SUM(t.total_amount) ORDER BY p.category ``` **Filter first, then group** Putting `WHERE quantity >= 3` before the GROUP BY keeps only bulk rows. Now SUM(total_amount) is the bulk revenue, but the non-bulk revenue is gone, so there is nothing to take half of. Every category that had any bulk order survives, so Books (one big bulk order dwarfed by a bigger non-bulk one) wrongly qualifies. **Conditional sum vs total** Leaving every row in the group and splitting bulk from non-bulk with a CASE inside the SUM preserves both the bulk revenue and the full category total. The half-of-total comparison becomes possible because the denominator is still intact, so Books correctly falls out and Clothing at exactly half stays out. > **Cost analysis** > > The join pairs 150M transactions with 40K products via a hash join on `product_id`. The GROUP BY folds everything into the roughly two dozen categories, and the two conditional sums ride along in the same aggregation pass, so there is no second scan and no self-join. The whole thing is one streaming aggregate over the joined rows. > **Common pitfall** > > Watch the boundaries. Writing the threshold as `>= 0.5 * SUM(total_amount)` lets a category sitting at exactly half slip in, when the ask is strictly more than half. Likewise, `quantity > 3` silently drops the three-unit orders that the definition counts as bulk. Both are one-character mistakes that quietly change the answer set. > **Interviewers watch for** > > The tell of a strong candidate is reaching for conditional aggregation the moment they hear 'what share of the total,' instead of two subqueries or a filtered scan. It signals they understand that a share needs both parts of the fraction computed over the same group in one pass. --- ## Common follow-up questions - How would you also report each category's exact bulk-revenue percentage, not just whether it clears half? _(Tests moving the conditional sums into the SELECT list alongside a computed ratio.)_ - What if the business wanted a configurable cutoff, say categories where bulk orders exceed 70 percent of revenue? _(Tests generalizing the fixed 0.5 threshold into a parameter or CTE-derived value.)_ - How does your query behave if some transactions have a missing quantity or amount? _(Tests reasoning about NULL quantity or total_amount and how CASE and SUM treat them.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_heavy_lifters) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.