Rolling 30-Day Latency SQL | Dataford Interview Questions

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.

Output

One row per hub, vendor tier, and delivery date.
Return hub_name, region, vendor_tier, delivery_date, and rolling_30_day_avg_latency_hours.
Include only non-null vendor tiers and completed deliveries. Order by region, hub name, vendor tier, and delivery date ascending.

Schema

regional_hubs

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

vendors

Column	Type	Description
vendor_idPK	INT	Unique vendor identifier
vendor_name	VARCHAR(100)	Vendor name
tier	VARCHAR(30)	Vendor service tier

deliveries

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

Your question is Rolling 30-Day Latency SQL. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem