Rolling 30-Day Latency SQL

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

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