Problem Statement
Describe two ways to return the top 3 products by revenue in each category and discuss tie handling.
Explanation
A window approach computes revenue per product, ranks within the category using ROW_NUMBER, and filters rows with rank less than or equal to three. ROW_NUMBER returns exactly three rows even if there are ties at the boundary.
If ties must be included, use RANK or DENSE_RANK and filter on rank less than or equal to three, which may return more than three rows per category. Choose the ranking function that matches the business rule.
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;