Your question is Rolling 30-Day Latency SQL. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query to calculate the rolling 30-day average delivery latency across regional hubs, broken down by vendor tier.
Use completed deliveries only, where latency is the elapsed time from shipped_at to delivered_at. Each 30-day period includes the current delivery date and the preceding 29 calendar days.
hub_name, region, vendor_tier, delivery_date, and rolling_30_day_avg_latency_hours.| Column | Type | Description |
|---|---|---|
| hub_idPK | INT | Unique regional hub identifier |
| hub_name | VARCHAR(100) | Regional hub name |
| region | VARCHAR(100) | Geographic region containing the hub |
| Column | Type | Description |
|---|---|---|
| vendor_idPK | INT | Unique vendor identifier |
| vendor_name | VARCHAR(100) | Vendor name |
| tier | VARCHAR(30) | Vendor service tier |
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique delivery identifier |
| hub_id | INT | Regional hub handling the delivery |
| vendor_id | INT | Vendor responsible for the delivery |
| shipped_at | TIMESTAMP | Shipment start timestamp |
| delivered_at | TIMESTAMP | Delivery completion timestamp |