Write a complex SQL query using window functions to calculate running totals and moving averages across multi-million-row partitioned tables.
Use the provided measurement, entity, and region tables. Restrict measurements to January 2025 and calculate daily totals before producing the analytical results.
Output
- One row per entity and measurement date, with
entity_id, entity_name, region_name, measured_at, daily_total, running_total, and moving_avg_3_rows.
- Include only entities with measurements in the requested period. The moving average uses the current row and up to two preceding dates for the same entity.
- Order by
region_name, entity_id, and measured_at ascending.
Output details
- Compute
running_total separately for each entity_id.
- Treat
NULL measurement amounts as zero when calculating daily_total.
- Use an inclusive lower bound of
DATE '2025-01-01' and an exclusive upper bound of DATE '2025-02-01'.
- Round
moving_avg_3_rows to two decimal places.
