Build a Dense 7-Calendar-Day Moving Average
Build daily revenue for every date in calendar_days and calculate a seven-calendar-day moving average.
- Window functions
- Joins
- Subqueries
- Aggregation
- Date analysis
Exercise brief
Understand the request
Revenue analytics engineer A seven-calendar-day trend must retain dates with no activity instead of collapsing to seven observed rows.
A seven-calendar-day trend must retain dates with no activity instead of collapsing to seven observed rows. Build daily revenue for every date in calendar_days and calculate a seven-calendar-day moving average.
Return
- Return calendar_date, daily_revenue, and rolling_avg_7_calendar_days.
- Order by calendar_date.
Constraints
- LEFT JOIN activity to the calendar table.
- Convert missing or all-NULL daily revenue to zero before windowing.
- Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and order by calendar_date.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
calendar_days
calendar_dateDATE
daily_activity
activity_idINTEGERactivity_dateDATErevenueDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Begin from calendar_days so dates without activity survive.
Hint 2
Aggregate after a LEFT JOIN and COALESCE the daily SUM to zero.
Hint 3
Window the dense daily rows with the current row and six preceding rows.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH daily AS (SELECT c.calendar_date, COALESCE(SUM(a.revenue), 0) AS daily_revenue FROM calendar_days c LEFT JOIN daily_activity a ON a.activity_date = c.calendar_date GROUP BY c.calendar_date) SELECT calendar_date, daily_revenue, ROUND(AVG(daily_revenue) OVER (ORDER BY calendar_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS rolling_avg_7_calendar_days FROM daily ORDER BY calendar_date;Why this works
A ROWS frame counts rows, not elapsed time. A calendar spine makes one row equal one calendar day, turning a seven-row frame into a truthful seven-calendar-day metric.
Success check
All ten calendar dates appear, zero-activity dates contribute zero, and each mature window spans exactly seven consecutive calendar rows.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| calendar_date | daily_revenue | rolling_avg_7_calendar_days |
|---|---|---|
| 2025-01-01 | 100 | 100 |
| 2025-01-02 | 75 | 87.5 |
| 2025-01-03 | 0 | 58.33 |
| 2025-01-04 | 0 | 43.75 |
| 2025-01-05 | 200 | 75 |
| 2025-01-06 | 0 | 62.5 |
| 2025-01-07 | 70 | 63.57 |
| 2025-01-08 | 0 | 49.29 |
| 2025-01-09 | 0 | 38.57 |
| 2025-01-10 | 40 | 44.29 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.