Task
You are given order and shipment data for a marketplace. Write a PostgreSQL query that measures whether shipping times improved after a carrier change by comparing the average delivery time before and after the carrier switch date for each carrier used on completed shipments.
Return each carrier’s name, the average shipping time in days before the change, the average shipping time in days after the change, and the difference between the two periods. Only include carriers with shipments in both periods, and sort by the largest improvement first.
Schema
| Table | Column | Type | Description |
|---|
orders | order_id | INT | Primary key for each order |
orders | created_at | DATE | Date the order was placed |
orders | status | VARCHAR(20) | Order status such as completed or cancelled |
shipments | shipment_id | INT | Primary key for each shipment |
shipments | order_id | INT | Foreign key to orders.order_id |
shipments | carrier_name | VARCHAR(50) | Carrier used for the shipment |
shipments | shipped_at | DATE | Date the package was shipped |
shipments | delivered_at | DATE | Date the package was delivered |
carrier_changes | carrier_name | VARCHAR(50) | Carrier name that changed in the routing setup |
carrier_changes | change_effective_date | DATE | Date the new carrier routing took effect |
Sample data
| orders | | | |
|---|
| order_id | created_at | status | |
| 1 | 2024-01-02 | completed | |
| 2 | 2024-01-03 | completed | |
| 3 | 2024-01-04 | cancelled | |
| 4 | 2024-01-05 | completed | |
| 5 | 2024-01-06 | completed | |
| shipments | | | | |
|---|
| shipment_id | order_id | carrier_name | shipped_at | delivered_at |
| 10 | 1 | SwiftShip | 2024-01-03 | 2024-01-06 |
| 11 | 2 | SwiftShip | 2024-01-04 | 2024-01-07 |
| 12 | 4 | SwiftShip | 2024-01-08 | 2024-01-10 |
| 13 | 5 | SwiftShip | 2024-01-09 | 2024-01-11 |
| 14 | 1 | ParcelPro | 2024-01-03 | 2024-01-08 |
| 15 | 2 | ParcelPro | 2024-01-04 | 2024-01-09 |
| 16 | 4 | ParcelPro | 2024-01-08 | 2024-01-12 |
| 17 | 5 | ParcelPro | 2024-01-09 | 2024-01-13 |
| 18 | 3 | SwiftShip | 2024-01-05 | 2024-01-07 |
| 19 | 5 | SlowMail | 2024-01-10 | 2024-01-16 |
| carrier_changes | | |
|---|
| carrier_name | change_effective_date | |
| SwiftShip | 2024-01-08 | |
| ParcelPro | 2024-01-08 | |
| SlowMail | 2024-01-08 | |
Expected output
| carrier_name | avg_days_before | avg_days_after | improvement_days |
|---|
| SwiftShip | 3.0 | 2.5 | 0.5 |
| ParcelPro | 5.0 | 4.0 | 1.0 |
