Task
QuickCart tracks delivery performance across shopper regions. Write a SQL query to find the average delivery time in hours per region for the last month using the orders and shoppers tables.
Requirements
- Join
orders to shoppers using shopper_id
- Only include orders delivered in the previous calendar month based on
delivered_at
- Exclude rows where
delivered_at or placed_at is NULL
- Return
region and avg_delivery_hours, rounded to 2 decimal places
- Sort results by
avg_delivery_hours descending, then region ascending
Table Definitions
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| shopper_id | INT | Shopper who placed the order |
| placed_at | TIMESTAMP | Time the order was placed |
| delivered_at | TIMESTAMP | Time the order was delivered |
| order_status | VARCHAR(20) | Current order status |
shoppers
| column | type | description |
|---|
| shopper_id | INT | Unique shopper identifier |
| shopper_name | VARCHAR(100) | Shopper name |
| region | VARCHAR(50) | Shopper region |
| signup_channel | VARCHAR(50) | Acquisition channel |
Sample Data
orders
| order_id | shopper_id | placed_at | delivered_at | order_status |
|---|
| 101 | 1 | 2024-05-03 09:00:00 | 2024-05-03 11:30:00 | delivered |
| 102 | 2 | 2024-05-10 14:00:00 | 2024-05-10 18:00:00 | delivered |
| 103 | 3 | 2024-05-12 08:15:00 | 2024-05-12 09:00:00 | delivered |
| 104 | 1 | 2024-04-28 19:00:00 | 2024-05-01 08:00:00 | delivered |
| 105 | 4 | 2024-05-20 12:00:00 | NULL | in_transit |
| 106 | 5 | 2024-05-22 10:00:00 | 2024-05-22 16:30:00 | delivered |
shoppers
| shopper_id | shopper_name | region | signup_channel |
|---|
| 1 | Ava Patel | West | organic |
| 2 | Ben Carter | East | paid_search |
| 3 | Chloe Kim | South | referral |
| 4 | Daniel Reed | North | organic |
| 5 | Elena Cruz | East | affiliate |
| 6 | Farah Ali | NULL | referral |
Expected Output
| region | avg_delivery_hours |
|---|
| West | 13.25 |
| East | 5.25 |
| North | 4.00 |
| South | 0.75 |
