Task
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.
Schema
| table | column | type | description |
|---|
| couriers | courier_id | INT | Primary key for each courier |
| couriers | courier_name | VARCHAR(100) | Courier display name |
| couriers | region | VARCHAR(50) | Operating region |
| deliveries | delivery_id | INT | Primary key for each delivery |
| deliveries | courier_id | INT | Foreign key to couriers.courier_id |
| deliveries | order_id | INT | Order identifier |
| deliveries | assigned_at | TIMESTAMP | When the delivery was assigned |
| deliveries | delivered_at | TIMESTAMP | When the delivery was completed, if completed |
| deliveries | promised_minutes | INT | Promised delivery time in minutes |
| deliveries | actual_minutes | INT | Actual delivery time in minutes |
| deliveries | status | VARCHAR(20) | Delivery status such as completed, canceled, or returned |
Sample data
| couriers | courier_id | courier_name | region |
|---|
| couriers | 1 | Maya Chen | Manhattan |
| couriers | 2 | Luis Patel | Brooklyn |
| couriers | 3 | Sam Rivera | Queens |
| couriers | 4 | Nina Shah | Manhattan |
| deliveries | delivery_id | courier_id | order_id | assigned_at | delivered_at | promised_minutes | actual_minutes | status |
|---|
| deliveries | 101 | 1 | 5001 | 2024-05-01 09:00:00 | 2024-05-01 09:22:00 | 30 | 22 | completed |
| deliveries | 102 | 1 | 5002 | 2024-05-02 10:00:00 | 2024-05-02 10:18:00 | 25 | 18 | completed |
| deliveries | 103 | 1 | 5003 | 2024-05-03 11:00:00 | 2024-05-03 11:40:00 | 35 | 40 | completed |
| deliveries | 104 | 1 | 5004 | 2024-05-04 12:00:00 | NULL | 30 | NULL | canceled |
| deliveries | 105 | 2 | 5005 | 2024-05-01 09:30:00 | 2024-05-01 09:55:00 | 20 | 25 | completed |
| deliveries | 106 | 2 | 5006 | 2024-05-02 10:15:00 | 2024-05-02 10:33:00 | 25 | 18 | completed |
| deliveries | 107 | 2 | 5007 | 2024-05-03 11:10:00 | 2024-05-03 11:28:00 | 20 | 18 | completed |
| deliveries | 108 | 2 | 5008 | 2024-05-04 12:05:00 | 2024-05-04 12:30:00 | 30 | 25 | completed |
| deliveries | 109 | 2 | 5009 | 2024-05-05 13:00:00 | 2024-05-05 13:20:00 | 20 | 20 | completed |
| deliveries | 110 | 3 | 5010 | 2024-05-01 08:45:00 | 2024-05-01 09:05:00 | 15 | 20 | completed |
| deliveries | 111 | 3 | 5011 | 2024-05-02 09:45:00 | NULL | 20 | NULL | returned |
| deliveries | 112 | 4 | 5012 | 2024-05-03 10:30:00 | 2024-05-03 10:44:00 | 15 | 14 | completed |
Expected output
| courier_name | region | completed_deliveries | on_time_rate | avg_actual_minutes | better_than_overall_avg |
|---|
| Luis Patel | Brooklyn | 5 | 0.40 | 21.2 | false |
| Maya Chen | Manhattan | 3 | 0.67 | 26.7 | true |
