Top 5 Best-Selling Products by Quantity Sold

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The merchandising team wants to know which products are flying off the shelves. Find the top 5 products by total units sold.

Tables

Table: products

| product_id | product_name | category_id | stock_quantity |
| --- | --- | --- | --- |
| 1 | T-Shirt | 1 | 50 |
| 2 | Jeans | 1 | 0 |
| 3 | Sneakers | 2 | 30 |
| 4 | Backpack | 2 | 0 |
| 5 | Watch | 3 | 15 |
| 6 | Sunglasses | 3 | 10 |

Table: categories

| category_id | category_name |
| --- | --- |
| 1 | Apparel |
| 2 | Footwear |
| 3 | Accessories |

Table: order_items

| item_id | product_id | quantity | unit_price | order_date |
| --- | --- | --- | --- | --- |
| 1 | 1 | 5 | 599 | 2024-05-01 |
| 2 | 3 | 2 | 2499 | 2024-05-01 |
| 3 | 2 | 3 | 1299 | 2024-05-02 |
| 4 | 5 | 1 | 1499 | 2024-05-02 |
| 5 | 3 | 4 | 2499 | 2024-05-03 |
| 6 | 1 | 2 | 599 | 2024-05-03 |
| 7 | 2 | 1 | 1299 | 2024-06-01 |
| 8 | 4 | 2 | 899 | 2024-06-01 |
| 9 | 5 | 4 | 1499 | 2024-06-02 |

Table: product_reviews

| review_id | product_id | rating |
| --- | --- | --- |
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 3 |
| 6 | 3 | 5 |
| 7 | 3 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 4 |
| 10 | 5 | 5 |

Expected Output (top 5)

| product_id | product_name | total_quantity |
| --- | --- | --- |
| 1 | T-Shirt | 7 |
| 3 | Sneakers | 6 |
| 5 | Watch | 5 |
| 2 | Jeans | 4 |
| 4 | Backpack | 2 |

• Return: product_id, product_name, total_quantity_sold
• Sort by total_quantity_sold descending
• Return only top 5 (use LIMIT 5)