How do you return the top 3 products per category by total revenue across many order lines?

## Why this is harder than top 3 salaries In the classic top-N query the metric is already a column: each employee row carries a salary, so you can rank the rows straight away. Here the metric is *derived*. No row of `order_lines` knows a product's total revenue; that number only exists once many lines have been summed. So the pattern gains a stage: **aggregate, then rank, then filter**. ## Stage one — build the metric ```sql SELECT p.category_id, ol.product_id, SUM(ol.quantity * ol.unit_price) AS revenue FROM order_lines ol JOIN products p ON p.product_id = ol.product_id GROUP BY p.category_id, ol.product_id ``` This collapses the line-level detail to one row per product, carrying its category. That grouped result is now shaped exactly like the simple case: one row per candidate, one metric column, one grouping column. ## Stage two — rank within the group ```sql ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rn ``` The partition is the category; the ordering is the freshly computed revenue. ## Stage three — filter `WHERE rn <= 3`, in a query level above the one that computed `rn`, because a window function is evaluated after `WHERE` and so cannot be filtered by it. Chained CTEs make all three stages visible: ```sql WITH product_revenue AS ( SELECT p.category_id, ol.product_id, SUM(ol.quantity * ol.unit_price) AS revenue FROM order_lines ol JOIN products p ON p.product_id = ol.product_id GROUP BY p.category_id, ol.product_id ), ranked AS ( SELECT pr.*, ROW_NUMBER() OVER (PARTITION BY pr.category_id ORDER BY pr.revenue DESC) AS rn FROM product_revenue pr ) SELECT category_id, product_id, revenue FROM ranked WHERE rn <= 3 ORDER BY category_id, rn; ``` ## Collapsing two stages into one Window functions are computed *after* grouping, on the rows `GROUP BY` produced. That means an aggregate call is a legal expression inside an `OVER` clause in a grouped query — the window sees the group rows, and `SUM(...)` is one of their values: ```sql SELECT category_id, product_id, revenue FROM ( SELECT p.category_id, ol.product_id, SUM(ol.quantity * ol.unit_price) AS revenue, ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY SUM(ol.quantity * ol.unit_price) DESC) AS rn FROM order_lines ol JOIN products p ON p.product_id = ol.product_id GROUP BY p.category_id, ol.product_id ) t WHERE rn <= 3; ``` This is one level shorter and behaves identically. Note two details: the aggregate must be repeated inside `OVER` rather than referenced by its alias, because a select-list alias is not visible to other expressions in the same select list under the standard; and you still need the enclosing query to filter `rn`. ## Getting the metric right before ranking it Ranking a wrong number produces a confidently wrong report, so the aggregation stage deserves scrutiny. Group by the same grain you intend to rank — here `(category_id, product_id)`; if you group by product alone and then partition by category, a product mapped to two categories will misbehave. Decide explicitly whether revenue should include discounts, cancelled lines or refunds, and put that decision in the inner query's `WHERE`, where it filters the raw lines before summing — not in the outer query, where it would arrive after the totals are already computed and the ranks already assigned. That placement question is the one interviewers actually probe. "Top 3 products per category in 2025" means restricting the *lines* to 2025 and then summing, so the filter belongs in the aggregation stage. Filtering after ranking would give you "whichever of the all-time top 3 happen to have 2025 rows", which is a different and usually unwanted report. ## Ties and the choice of ranking function Summed money rarely ties exactly, but summed counts often do, and the choice still matters: `ROW_NUMBER()` returns exactly three products per category and picks arbitrarily among equals; `RANK()` returns everyone tied for third. Add a deterministic tiebreaker — `ORDER BY revenue DESC, product_id` — if the report must be stable between runs. ## Variations Swap `ROW_NUMBER` for `RANK` to keep ties; change `rn <= 3` to `rn = 1` for the single best seller per category; add a second partition column to get the top 3 per category *per month* by including the month in both the `GROUP BY` and the `PARTITION BY`. The three-stage skeleton stays the same.