Biggest buyer per category
Hard
For each product category, show the customer name who bought the most units of it, and the units. (Break ties by name, alphabetically.)
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Your query✔ Solved
Show the expected result
| category | name | units |
|---|---|---|
| Books | Aarav Sharma | 11 |
| Electronics | Sita Gurung | 13 |
| Furniture | Zoe Clarke | 11 |
| Grocery | Aarav Sharma | 6 |
| Stationery | Grace Lee | 6 |
5 rows
Show a solution
WITH u AS (SELECT p.category, c.name, SUM(i.quantity) AS units FROM order_items i JOIN products p ON p.product_id = i.product_id JOIN orders o ON o.order_id = i.order_id JOIN customers c ON c.customer_id = o.customer_id GROUP BY p.category, c.customer_id), r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, name) AS rn FROM u) SELECT category, name, units FROM r WHERE rn = 1;
SQL used
New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.