SQL interview question: Best Seller per Category
Best Seller in Each Category
Merchandising wants to feature the top-selling product from each category on the homepage.
Find the product with the highest total quantity sold in each category.
Return category, product_name, and quantity_sold. If two products tie, keep the one with the lower product_id. Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY quantity DESC).
Skills: RANK() or ROW_NUMBER() with PARTITION BY
Tables
order_items 19 rows
- id integer PK
- order_id integer → orders
- product_id integer → products
- quantity integer
| id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 3 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 2 | 6 | 1 |
products 20 rows
- product_id integer PK
- product_name text
- category text
- price real
- rating real
| product_id | product_name | category | price | rating |
|---|---|---|---|---|
| 1 | MacBook Pro | Electronics | 1999.99 | 4.8 |
| 2 | iPad Air | Electronics | 599.99 | 4.8 |
| 3 | AirPods Pro | Electronics | 249.99 | 4.7 |