🏆 Only the Top Three Survive.
Inside Netflix's Content Strategy team, the product managers are locked in a heated debate. Every category manager claims their product line is the revenue king. The spreadsheets are contradictory. The egos are enormous.
You have been brought in as the neutral arbiter of truth. Your weapon: SQL window functions.
Your task: For each product category, find the top 3 products by total revenue. If two products have the same revenue, they share the same rank — use DENSE_RANK. Return category, product name, total revenue, and rank.
This is where most intermediate analysts stumble. They write a GROUP BY, get the totals, then don't know how to rank within each group. The answer lives inside a window function with PARTITION BY.
products Table:
| Column Name | Type |
|---|---|
| product_id | integer |
| name | text |
| category | text |
| price | integer |
order_items Table:
| Column Name | Type |
|---|---|
| item_id | integer |
| order_id | integer |
| product_id | integer |
| quantity | integer |
Expected Outcome (Illustrative Sample):
| category | name | total_revenue | rnk |
|---|---|---|---|
| Education | SQL Bootcamp | 4500 | 1 |
Only 3 survive per category. Let the rankings begin.