15. Top two products per category
Asked in screens shaped like: Amazon, Walmart, Target
Top-N per group is asked in almost every SQL screen. Return the two best-selling products inside each category by units, with a deterministic tie-break.
Requirements
- Columns: category, product_id, name, units
- units is SUM(qty) from order_items
- At most two rows per category, ranked by units descending then product_id
- Order by category, then units descending, then product_id
Expected output
category, 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
Two stages: aggregate units, then rank inside category.
Hint 2
PARTITION BY category resets the counter for each category.
Hint 3
Filter rn <= 2 outside the CTE that created rn.
WITH units AS (
SELECT p.category, 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.category, p.product_id, p.name
),
ranked AS (
SELECT category, product_id, name, units,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, product_id) AS rn
FROM units
)
SELECT category, product_id, name, units
FROM ranked
WHERE rn <= 2
ORDER BY category, units DESC, product_id;Why it works
ROW_NUMBER caps each category at two rows no matter how many ties exist. Use DENSE_RANK instead when the business genuinely wants every tied product included.
Run your query to see the result set.
Submit to run the checks.
Why it works
ROW_NUMBER caps each category at two rows no matter how many ties exist. Use DENSE_RANK instead when the business genuinely wants every tied product included.
Pro problem
Hard 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