2. Top products by units sold
Asked in screens shaped like: Amazon, Walmart, Instacart
Merchandising wants units sold per product. The quantity lives on the line items, not the order header — summing orders.amount here is the classic wrong answer because an order can carry several products.
Requirements
- Columns: product_id, name, units
- units is SUM(qty) from order_items
- Order by units descending, then product_id ascending
Expected output
product_id, name, units
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
order_items
~250 rows
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGER— joins orders
- product_idTEXT— joins products
- qtyINTEGER
- unit_priceREAL
products
~5 rows
Product dimension across three categories.
- product_idTEXT— P01–P05
- nameTEXT
- categoryTEXT— saas, goods, services
- priceREAL
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
Join order_items to products on product_id.
Hint 2
Group by both product_id and name so the name survives aggregation.
Hint 3
Never touch orders.amount for a per-product unit count.
SELECT p.product_id, p.name, SUM(i.qty) AS units
FROM order_items i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.product_id, p.name
ORDER BY units DESC, p.product_id;Why it works
Line items are the correct grain for unit counts. Joining through the order header would multiply quantities by the number of lines on each order.
Run your query to see the result set.
Submit to run the checks.
Why it works
Line items are the correct grain for unit counts. Joining through the order header would multiply quantities by the number of lines on each order.
Pro problem
Medium problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems