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 |