Problem Statement
Describe a query to return the top 3 products by revenue in each category and explain tie handling.
Explanation
Use a window function to rank rows inside each category by revenue. ROW_NUMBER gives exactly three rows even with ties; RANK or DENSE_RANK include all ties but may return more than three rows per category.
Pick the ranking function that matches the business rule. Then filter on the rank in an outer query for a clean result.
Code Solution
SolutionRead Only
SELECT category_id, product_id, revenue
FROM (
SELECT category_id, product_id, SUM(amount) AS revenue,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY SUM(amount) DESC) AS rn
FROM sales
GROUP BY category_id, product_id
) t
WHERE rn <= 3;Practice Sets
This question appears in the following practice sets:
