Task
You are given delivery records and region metadata. Write a PostgreSQL query that compares delivery performance across regions over time by returning each region’s weekly on-time rate, the prior week’s on-time rate, and the week-over-week change. Use window functions to calculate the prior week value and the change, and sort the final output chronologically by week and region.
Schema
| table | column | type | description |
|---|
deliveries | delivery_id | INT | Unique delivery record ID |
deliveries | region_id | INT | Region for the delivery |
deliveries | delivered_at | DATE | Delivery completion date |
deliveries | promised_minutes | INT | Promised delivery time in minutes |
deliveries | actual_minutes | INT | Actual delivery time in minutes |
deliveries | status | VARCHAR(20) | Delivery status |
regions | region_id | INT | Unique region ID |
regions | region_name | VARCHAR(100) | Region name |
Sample data
deliveries
| delivery_id | region_id | delivered_at | promised_minutes | actual_minutes | status |
|---|
| 1 | 1 | 2024-01-02 | 30 | 28 | delivered |
| 2 | 1 | 2024-01-04 | 30 | 35 | delivered |
| 3 | 1 | 2024-01-09 | 30 | 27 | delivered |
| 4 | 2 | 2024-01-03 | 25 | 24 | delivered |
| 5 | 2 | 2024-01-10 | 25 | 29 | delivered |
| 6 | 2 | 2024-01-10 | 25 | 31 | canceled |
| 7 | 3 | 2024-01-03 | 40 | 38 | delivered |
| 8 | 3 | 2024-01-11 | 40 | 44 | delivered |
regions
| region_id | region_name |
|---|
| 1 | North Bay |
| 2 | South Bay |
| 3 | East Bay |
| 4 | West Bay |
Expected output
| week_start | region_name | on_time_rate | prior_week_on_time_rate | wow_change |
|---|
| 2024-01-01 | East Bay | 1.0000 | null | null |
| 2024-01-01 | North Bay | 0.5000 | null | null |
| 2024-01-01 | South Bay | 1.0000 | null | null |
| 2024-01-08 | East Bay | 0.0000 | 1.0000 | -1.0000 |
| 2024-01-08 | North Bay | 1.0000 | 0.5000 | 0.5000 |
| 2024-01-08 | South Bay | 1.0000 | 1.0000 | 0.0000 |
