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
- Produce one row per calendar day between
2024-01-01 and 2024-01-07 (inclusive) using the dim_dates table.
- Count bookings by the date portion of
booked_at (treat booked_at as UTC).
- Include days with 0 bookings.
- Compute
ma_30d_bookings as the average of daily_bookings over the current day and the prior 29 days (a 30-day trailing window).
- Return columns:
dt, daily_bookings, ma_30d_bookings.
- Order results by
dt ascending.
Table Definitions
dim_dates
| column | type | description |
|---|
| dt | DATE | Calendar date (one row per day) |
task_bookings
| column | type | description |
|---|
| booking_id | BIGINT | Unique booking identifier |
| task_id | BIGINT | Task being booked |
| booked_at | TIMESTAMP | Timestamp when the booking was created (UTC) |
| status | VARCHAR(20) | Booking state (e.g., confirmed, cancelled) |
Sample Data
dim_dates
| dt |
|---|
| 2024-01-01 |
| 2024-01-02 |
| 2024-01-03 |
| 2024-01-04 |
| 2024-01-05 |
task_bookings
| booking_id | task_id | booked_at | status |
|---|
| 9001 | 501 | 2024-01-01 10:15:00 | confirmed |
| 9002 | 502 | 2024-01-01 18:40:00 | confirmed |
| 9003 | 503 | 2024-01-03 09:05:00 | cancelled |
| 9004 | 504 | 2024-01-04 12:00:00 | confirmed |
| 9005 | 505 | 2024-01-04 17:20:00 | confirmed |
Expected Output (for sample data)
Assume all statuses count as bookings unless otherwise specified.
| dt | daily_bookings | ma_30d_bookings |
|---|
| 2024-01-01 | 2 | 2.0000 |
| 2024-01-02 | 0 | 1.0000 |
| 2024-01-03 | 1 | 1.0000 |
| 2024-01-04 | 2 | 1.2500 |
| 2024-01-05 | 0 | 1.0000 |
