Task
ShopWave wants to identify its best-selling items in California for the previous calendar month. Write a SQL query to return the top 3 most popular items based on total quantity sold.
Requirements
- Join
orders to items using item_id.
- Filter to orders where
state = 'CA' and the order_date falls in the previous calendar month.
- Aggregate total quantity sold per item.
- Return the top 3 items ordered by total quantity sold descending. Break ties by
item_name ascending.
Table Definitions
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| item_id | INT | Item purchased |
| quantity | INT | Units sold in the order |
| state | VARCHAR(2) | Shipping state |
| order_date | DATE | Date the order was placed |
items
| column | type | description |
|---|
| item_id | INT | Unique item identifier |
| item_name | VARCHAR(100) | Item name |
| category | VARCHAR(50) | Item category |
Sample Data
orders
| order_id | item_id | quantity | state | order_date |
|---|
| 1 | 101 | 3 | CA | 2024-05-03 |
| 2 | 102 | 5 | CA | 2024-05-05 |
| 3 | 101 | 2 | NV | 2024-05-06 |
| 4 | 103 | 4 | CA | 2024-05-10 |
| 5 | 104 | 1 | CA | 2024-04-29 |
items
| item_id | item_name | category |
|---|
| 101 | Wireless Mouse | Electronics |
| 102 | Yoga Mat | Fitness |
| 103 | Water Bottle | Fitness |
| 104 | Desk Lamp | Home |
Expected Output
| item_name | total_quantity_sold |
|---|
| Yoga Mat | 8 |
| Wireless Mouse | 7 |
| Water Bottle | 5 |
