Task
You are given weekly operational performance data for a delivery marketplace. Write a PostgreSQL query that compares each week's metric to the prior week for the same metric and returns the absolute change and percentage change. Use a window function such as LAG or LEAD to measure the shift over time.
Schema
| table | column | type | description |
|---|
weekly_ops_metrics | metric_date | DATE | Week-ending date for the metric snapshot |
weekly_ops_metrics | metric_name | VARCHAR(100) | Name of the operational metric |
weekly_ops_metrics | metric_value | NUMERIC(12,2) | Measured value for that week |
Sample data
| metric_date | metric_name | metric_value |
|---|
| 2024-01-07 | on_time_delivery_rate | 96.20 |
| 2024-01-14 | on_time_delivery_rate | 95.10 |
| 2024-01-21 | on_time_delivery_rate | 95.80 |
| 2024-01-07 | average_delivery_time_minutes | 42.50 |
| 2024-01-14 | average_delivery_time_minutes | 44.00 |
| 2024-01-21 | average_delivery_time_minutes | 41.80 |
Expected output
| metric_date | metric_name | metric_value | prior_metric_value | absolute_change | pct_change |
|---|
| 2024-01-07 | average_delivery_time_minutes | 42.50 | | | |
| 2024-01-14 | average_delivery_time_minutes | 44.00 | 42.50 | 1.50 | 3.53 |
| 2024-01-21 | average_delivery_time_minutes | 41.80 | 44.00 | -2.20 | -5.00 |
| 2024-01-07 | on_time_delivery_rate | 96.20 | | | |
| 2024-01-14 | on_time_delivery_rate | 95.10 | 96.20 | -1.10 | -1.14 |
| 2024-01-21 | on_time_delivery_rate | 95.80 | 95.10 | 0.70 | 0.74 |
