Advanced
Case 38 of 50
Case 038: Best Seller Per Category
order_itemsproducts
Merchandising wants a category-level view for the homepage, and the manager relays the request.
"For every category, could you tell me the single best-selling product? Not a ranked list — just the winner."
The data Alex is looking at
order_items
| order_id | product_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 101 | 2 | 1 |
| 102 | 3 | 1 |
| 103 | 2 | 3 |
| 104 | 5 | 1 |
| … 29 more rows | ||
products
| id | name | category | price |
|---|---|---|---|
| 1 | Keyboard | Accessories | 2500 |
| 2 | Mouse | Accessories | 1200 |
| 3 | Headphones | Audio | 3500 |
| 4 | Monitor | Displays | 12000 |
| 5 | Laptop | Computers | 60000 |
| … 5 more rows | |||
TASK
Help Alex answer the manager:
“Find the best-selling product (by units sold) in each category, showing how many units it sold.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH product_sales AS (
SELECT
products.category,
products.name,
SUM(order_items.quantity) AS units_sold
FROM order_items
JOIN products ON products.id = order_items.product_id
GROUP BY products.category, products.name
),
ranked AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC, name ASC) AS rn
FROM product_sales
)
SELECT category, name, units_sold
FROM ranked
WHERE rn = 1;
What that query returns
Case Debrief
Top-N per group (ROW_NUMBER + PARTITION BY)
- "Top N per group" is one of the most common real-world SQL questions — PARTITION BY resets the ranking per group, so rn = 1 gives you the winner in each one.
- Quick tip: ORDER BY units_sold DESC, name ASC — the second key only matters when the first one ties. Without it, two products tied for the top spot could come back in either order depending on the database's internal row order, which isn't something your query should ever depend on.