Task
You are given Uber delivery records and asked to compare last week’s average delivery time against the prior week for each market. Write a PostgreSQL query that returns one row per market with the two weekly averages, the absolute change, and the percent change. Use the two most recent completed weeks in the data, not the current partial week.
Schema
| table | column | type | description |
|---|
deliveries | delivery_id | BIGINT | Unique delivery record identifier |
deliveries | market | VARCHAR(100) | Market name |
deliveries | delivered_at | TIMESTAMP | Delivery completion timestamp |
deliveries | delivery_time_minutes | NUMERIC(10,2) | End-to-end delivery time in minutes |
deliveries | status | VARCHAR(20) | Delivery status such as completed or canceled |
Sample data
| delivery_id | market | delivered_at | delivery_time_minutes | status |
|---|
| 101 | San Francisco | 2024-05-06 10:15:00 | 28.0 | completed |
| 102 | San Francisco | 2024-05-13 11:20:00 | 34.0 | completed |
| 103 | San Francisco | 2024-05-14 09:05:00 | 31.0 | completed |
| 104 | New York | 2024-05-06 12:10:00 | 41.0 | completed |
| 105 | New York | 2024-05-13 13:45:00 | 39.0 | completed |
| 106 | New York | 2024-05-15 08:30:00 | 44.0 | canceled |
| 107 | Chicago | 2024-05-07 14:00:00 | 25.0 | completed |
| 108 | Chicago | 2024-05-13 15:10:00 | 29.0 | completed |
| 109 | Chicago | 2024-05-20 16:25:00 | 27.0 | completed |
| 110 | Miami | 2024-05-13 18:40:00 | 52.0 | completed |
| 111 | Miami | 2024-05-20 19:05:00 | 48.0 | completed |
| 112 | Miami | 2024-05-20 20:10:00 | 50.0 | canceled |
Expected output
| market | prior_week_avg_minutes | last_week_avg_minutes | abs_change_minutes | pct_change |
|---|
| Chicago | 25.0 | 29.0 | 4.0 | 16.0 |
| Miami | 52.0 | 48.0 | -4.0 | -7.69 |
| New York | 41.0 | 39.0 | -2.0 | -4.88 |
| San Francisco | 28.0 | 32.5 | 4.5 | 16.07 |
