Use ROW_NUMBER() with PARTITION BY to solve top-N per group problems
For the quarterly review, I need the top 2 products by revenue in each category (normalized). Show the category, product name, total revenue, and the rank within its category. Only the top 2 per category — no more.
Each hint you reveal reduces the XP you can earn. Try the query first.
Use ROW_NUMBER() OVER (PARTITION BY LOWER(category) ORDER BY total_revenue DESC) to rank within each category
You need to aggregate revenue per product first in a subquery, then apply the window function, then filter to rank <= 2 in an outer query
Three layers: innermost aggregates, middle applies ROW_NUMBER, outermost filters WHERE rn <= 2