Your question is Analyze Delivery Performance with Subqueries. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given delivery-level data and courier metadata. Write a PostgreSQL query that returns each courier’s on-time delivery rate and average delivery time for completed deliveries in the last 30 days, but only for couriers who completed at least 5 deliveries. Also include a flag showing whether each courier’s average delivery time is better than the overall average for the same period. Use subqueries to keep the analysis readable.
| Column | Type | Description |
|---|---|---|
| courier_idPK | INT | Primary key for each courier |
| courier_name | VARCHAR(100) | Courier display name |
| region | VARCHAR(50) | Operating region |
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Primary key for each delivery |
| courier_id | INT | Foreign key to couriers.courier_id |
| order_id | INT | Order identifier |
| assigned_at | TIMESTAMP | When the delivery was assigned |
| delivered_at | TIMESTAMP | When the delivery was completed, if completed |
| promised_minutes | INT | Promised delivery time in minutes |
| actual_minutes | INT | Actual delivery time in minutes |
| status | VARCHAR(20) | Delivery status such as completed, canceled, or returned |