Return the top 3 products by sales in every category
Table sales(category, product_id, quantity, unit_price). For each category return its three best-selling products by total revenue, where revenue is SUM(quantity * unit_price). One product per row, at most three per category.
-- top 3 products by revenue within each categoryWrite the query.
Aggregate revenue by product, rank per category, keep top ranks outside: ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) with WHERE rn <= 3. GROUP BY ... LIMIT 3 caps globally, not per category.
- ✗Thinking
LIMITcaps rows per category rather than globally - ✗Using
HAVING COUNT(*)as if it selected the top rows - ✗Assuming
RANKalways yields exactly three rows before a gap
- →How would ties for third place change the row count with
RANKvsROW_NUMBER? - →Why can't
GROUP BY ... LIMIT 3solve the per-category cap?
Aggregate revenue per product, rank within each category, then keep the first three ranks in an outer query. A window result cannot be filtered in WHERE, so a subquery is required:
SELECT category, product_id, revenue
FROM (
SELECT category, product_id,
SUM(quantity * unit_price) AS revenue,
ROW_NUMBER() OVER (PARTITION BY category
ORDER BY SUM(quantity * unit_price) DESC) AS rn
FROM sales
GROUP BY category, product_id
) t
WHERE rn <= 3
ORDER BY category, rn;GROUP BY category ... LIMIT 3 does not work: LIMIT is global and trims the whole result to three rows, not three per category. ROW_NUMBER guarantees exactly three rows; RANK/DENSE_RANK may return more when there are ties for third place.
