Problem
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
orderstoshoppersusingshopper_id - Only include orders delivered in the previous calendar month based on
delivered_at - Exclude rows where
delivered_atorplaced_atisNULL - Return
regionandavg_delivery_hours, rounded to 2 decimal places - Sort results by
avg_delivery_hoursdescending, thenregionascending
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 |
Schema
orders
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Primary key for each order |
| shopper_id | INT | References the shopper who placed the order |
| placed_at | TIMESTAMP | Timestamp when the order was placed |
| delivered_at | TIMESTAMP | Timestamp when the order was delivered |
| order_status | VARCHAR(20) | Current order status |
shoppers
| Column | Type | Description |
|---|---|---|
| shopper_idPK | INT | Primary key for each shopper |
| shopper_name | VARCHAR(100) | Full name of the shopper |
| region | VARCHAR(50) | Region assigned to the shopper |
| signup_channel | VARCHAR(50) | Marketing channel used during signup |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
