Operations wants units sold and revenue per sku, but only for products that sold at least two units. Return sku, units_sold, and revenue_cents. Sort by units_sold descending, then sku.
Requirements
Keep the supplied order_lines table unchanged.
Return sku, units_sold, and revenue_cents.
Define units_sold as SUM(qty) and revenue_cents as SUM(qty * unit_cents).
Keep only groups whose units_sold is at least 2.
Do not include MUG-9.
Sort units_sold descending, then sku ascending.
Example
Input
Keep order_lines, then group and filter with HAVING.
Each sku is a group. SUM(qty) is units; SUM(qty * unit_cents) is revenue at the sold price. MUG-9 sold one unit, so HAVING SUM(qty) >= 2 removes it. WHERE qty >= 2 would be the wrong sentence: it would drop the two separate NB-1 lines of qty 1 and keep only the PEN-2 line of qty 2. HAVING runs after grouping. Pen units are 3 and notebook units are 2, so pens sort first even though notebook revenue is higher. The second ORDER BY sku is unused here but keeps ties stable. This is the same grain decision as every stock or sales rollup: filter rows with WHERE, filter groups with HAVING.
Why this pattern matters
Demand reports are groups. Filtering lines before grouping answers a different question than filtering groups after SUM, which is how a one-unit mug disappears from a two-unit threshold without deleting valid notebook sales.
Solution walkthrough
GROUP BY sku.
SUM qty and SUM qty * unit_cents.
HAVING SUM(qty) >= 2 drops MUG-9.
Order 3-unit pens above 2-unit notebooks.
Common mistakes to avoid
WHERE qty >= 2, which deletes the two notebook lines of qty 1.
SUM(unit_cents) without multiplying qty.
Ordering by revenue when the spec asked for units_sold.
What this exercise teaches
GROUP BY a grain
SUM quantities and money
Filter groups with HAVING rather than WHERE
A PRACTICAL PLAN
Work through Summarize demand by product with intent.
Translate the contract.Turn the requirements into a short checklist before editing your-solution.sql.
Use the example as evidence.Predict the result for the supplied input, then add one boundary case such as an empty value, a limit, or unexpected input.
Review the implementation.Compare your choices against solution.sql only after a real attempt.
EXERCISE FAQ
Before you move on.
What does “Summarize demand by product” teach?
This intermediate SQL exercise focuses on GROUP BY a grain, SUM quantities and money, Filter groups with HAVING rather than WHERE. Its requirements define the exact behavior to implement before you write code.
How should I validate this SQL solution?
Start with the displayed example input and expected output, then test a boundary case suggested by the requirements. Open your-solution.sql and select Run code. The browser creates a fresh isolated SQLite database, executes the complete script, and compares its result rows with the expected report.
When should I open the reference solution?
Attempt Summarize demand by product first. Then open the read-only solution file to compare the contract, edge-case handling, and implementation choices—not simply to copy the final code.
