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.
orders to items using item_id.state = 'CA' and the order_date falls in the previous calendar month.item_name ascending.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 |
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 |
| item_name | total_quantity_sold |
|---|---|
| Yoga Mat | 8 |
| Wireless Mouse | 7 |
| Water Bottle | 5 |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| item_id | INT | References the purchased item |
| quantity | INT | Number of units sold in the order |
| state | VARCHAR(2) | Shipping state code |
| order_date | DATE | Date the order was placed |
| Column | Type | Description |
|---|---|---|
| item_idPK | INT | Unique item identifier |
| item_name | VARCHAR(100) | Display name of the item |
| category | VARCHAR(50) | Item category |
| item_id | item_name | category |
|---|---|---|
| 101 | Wireless Mouse | Electronics |
| 102 | Yoga Mat | Fitness |
| 103 | Water Bottle | Fitness |
| 104 | Desk Lamp | Home |
| 105 | Notebook Set | Office |
| 106 | Bluetooth Speaker | Electronics |
| 107 | Coffee Grinder | |
| 108 | Phone Stand | Accessories |
| 109 | Resistance Bands | Fitness |
| 110 | LED Monitor | Electronics |
| 1 | LED Monitor | Storage |
| 2 | Laptop Sleeve | Fitness |
| 3 | USB-C Hub | Kitchen |
| 4 | Wireless Mouse | Furniture |
| 5 | Travel Backpack | Computer Peripherals |
| 6 | Bluetooth Speaker | Stationery |
| 7 | Webcam | Hydration |
| 8 | Desk Lamp | Coffee |
| 9 | Wireless Charger | null |
| 10 | Phone Stand | Kitchen |
| 11 | Wireless Charger | null |
| 12 | Wireless Charger | Coffee |
| 13 | Standing Desk Mat | Fitness |
| 14 | Noise Cancelling Headphones | Home |
| 15 | Ceramic Mug | Fitness |
| order_id | item_id | quantity | state | order_date |
|---|---|---|---|---|
| 1 | 102 | 2 | CA | 2026-04-12 |
| 2 | 101 | 1 | CA | 2026-04-02 |
| 3 | 103 | 5 | CA | 2026-04-18 |
| 4 | 101 | 4 | CA | 2026-04-25 |
| 5 | 104 | 3 | CA | 2026-04-09 |
| 6 | 102 | 6 | NV | 2026-04-11 |
| 7 | 105 | 7 | CA | 2026-03-30 |
| 8 | 102 | 3 | CA | 2026-04-28 |
| 9 | 106 | 10 | CA | 2026-04-14 |
| 10 | 107 | 8 | CA | 2026-04-20 |
| 11 | 103 | 1 | 2026-04-21 | |
| 12 | 9 | CA | 2026-04-22 | |
| 1 | 80 | 80 | MI | 2026-04-04 |
| 2 | 27 | 36 | MA | 2026-04-19 |
| 3 | 93 | 68 | MA | 2026-04-26 |
| 4 | 42 | 93 | CO | 2026-04-19 |
| 5 | 36 | 17 | WA | 2026-05-01 |
| 6 | 5 | 79 | SC | 2026-04-19 |
| 7 | 47 | 5 | VA | 2026-04-02 |
| 8 | 99 | 34 | IL | 2026-03-31 |
| 9 | 86 | 52 | GA | 2026-03-30 |
| 10 | 15 | 46 | null | 2026-04-16 |
| 11 | 96 | 48 | NC | 2026-04-13 |
| 12 | 82 | 40 | UT | 2026-04-12 |
| 13 | 36 | 8 | null | 2026-04-16 |
| item_name | total_quantity_sold |
|---|---|
| Bluetooth Speaker | 10 |
| Coffee Grinder | 8 |
| Water Bottle | 5 |
