Home / SQL / Practice problems / Hard

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
Show the expected result
categorynameunits
BooksAarav Sharma11
ElectronicsSita Gurung13
FurnitureZoe Clarke11
GroceryAarav Sharma6
StationeryGrace Lee6

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.