The best-selling product in each category
Hard · Online shop database · Window functions, WITH (CTE), JOIN
For each category, find the product that sold the most units. Units sold is the sum of quantity over the order items of orders that were not cancelled. When products tie for the most units in a category, list all of them.
Return category, the product name and the units sold, in that order, sorted by category, then product name.
Expected result
| category | name | units |
|---|---|---|
| Books | The Quiet Algorithm | 4 |
| Electronics | Noise Cancelling Headphones | 3 |
| Electronics | USB-C Hub | 3 |
| Electronics | Wireless Mouse | 3 |
| Home | Ceramic Mug | 9 |
| Sports | Steel Water Bottle | 5 |
| Stationery | Spiral Notebook | 13 |
The online shop tables
customers: 18 rows, first 5 shown
| id | name | city | country | signup_date | phone |
|---|---|---|---|---|---|
| 1 | Ravi Kumar | Bengaluru | India | 2025-01-12 | +91 98450 12345 |
| 2 | Emily Johnson | Austin | USA | 2025-01-25 | +1 512 555 0142 |
| 3 | Hiroshi Sato | Osaka | Japan | 2025-02-08 | NULL |
| 4 | Ana Souza | Sao Paulo | Brazil | 2025-02-19 | +55 11 91234 5678 |
| 5 | Priya Sharma | Mumbai | India | 2025-03-03 | +91 99200 54321 |
order_items: 40 rows, first 5 shown
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 22.99 |
| 2 | 1 | 9 | 3 | 3.99 |
| 3 | 2 | 4 | 1 | 189 |
| 4 | 2 | 16 | 2 | 15.99 |
| 5 | 3 | 2 | 1 | 89.5 |
orders: 28 rows, first 5 shown
| id | customer_id | order_date | status |
|---|---|---|---|
| 1 | 1 | 2025-02-03 | delivered |
| 2 | 2 | 2025-02-14 | delivered |
| 3 | 3 | 2025-03-01 | delivered |
| 4 | 1 | 2025-03-22 | cancelled |
| 5 | 4 | 2025-04-05 | delivered |
products: 18 rows, first 5 shown
| id | name | category | price | stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 24.99 | 120 |
| 2 | Mechanical Keyboard | Electronics | 89.5 | 45 |
| 3 | USB-C Hub | Electronics | 39 | 80 |
| 4 | Noise Cancelling Headphones | Electronics | 199 | 25 |
| 5 | Intro to Statistics | Books | 48 | 60 |
-- Write your SQL here