30-Day Moving Average of Bookings

Business Context

You’re on the analytics engineering team at a two-sided logistics marketplace (think “Uber for warehouse labor”) that processes ~2M task bookings per month across multiple metro areas. Operations leaders track booking volume to forecast staffing needs and to detect demand shocks (weather events, promotions, large enterprise contracts). Daily booking counts are noisy, so the team uses a 30-day moving average as the primary trend metric in dashboards.

Bookings are stored at the event level (one row per booking). However, the dashboard needs a daily time series that includes days with zero bookings (e.g., holidays or system outages), because missing dates can make the moving average misleading.

Task

Write a SQL query to compute the daily booking count and the 30-day moving average of daily bookings.

Requirements

1. Produce one row per calendar day between 2024-01-01 and 2024-01-07 (inclusive) using the dim_dates table.
2. Count bookings by the date portion of booked_at (treat booked_at as UTC).
3. Include days with 0 bookings.
4. Compute ma_30d_bookings as the average of daily_bookings over the current day and the prior 29 days (a 30-day trailing window).
5. Return columns: dt, daily_bookings, ma_30d_bookings.
6. Order results by dt ascending.