Calculate rolling averages without hiding missing dates

A three-row moving average is a three-day moving average only when the input has exactly one row for every relevant day. If the table contains only days with purchases, a row-based frame skips inactive dates and changes the denominator.

The solution is to establish a complete daily calendar, join the daily measure to it, and decide whether missing source rows mean zero activity or incomplete data. A calendar can supply dates; it cannot prove that your event source is complete.

The advanced SQL lab treats its January–April purchase history as complete for this exercise. The first purchase occurs on January 5, and the next on January 7. The intervening days have zero purchase value under that stated assumption.

Aggregate to one row per day

Start by summarizing purchases rather than calculating a window directly over transaction rows:

SELECT purchased_at AS day, SUM(amount_paise) AS daily_value_paise
FROM purchases
GROUP BY purchased_at
ORDER BY purchased_at;

The fixture stores dates, so this grouping is already at daily grain. Timestamped production data needs a business-date derivation with an explicit timezone.

The result contains only dates with purchases. A moving average over three such rows would mean “the last three purchase dates,” which can span much more than three calendar days.

Join to the calendar and expose window size

WITH daily AS (
    SELECT purchased_at AS day, SUM(amount_paise) AS daily_value_paise
    FROM purchases
    GROUP BY purchased_at
), complete_days AS (
    SELECT c.day, COALESCE(d.daily_value_paise, 0) AS daily_value_paise
    FROM calendar AS c
    LEFT JOIN daily AS d ON d.day = c.day
    WHERE c.day >= '2026-01-01' AND c.day < '2026-01-08'
)
SELECT day, daily_value_paise,
       COUNT(*) OVER (
           ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS observed_window_days,
       AVG(1.0 * daily_value_paise) OVER (
           ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_average_paise
FROM complete_days
ORDER BY day;

January 5's moving average is approximately 3,333.33 paise: its 10,000-paise purchase is averaged with zero values on January 3 and 4. January 6 remains 3,333.33. January 7 becomes approximately 6,666.67 because the three-day window contains 10,000, zero and 10,000.

The first result uses one day and the second uses two. They are partial windows. Display the day count, suppress partial values or load earlier history if a full three-day estimate is required. Do not label every row identically while hiding the changing denominator.

SQLite's frame rules specify which rows enter each calculation. SQLite window-function documentation.

Zero filling requires evidence

If January 6's source file failed to arrive, zero would be an invented observation. The right result might be unknown, or the report may need to stop until the source is complete.

Keep a completeness indicator separate from the numeric measure. For a strict three-day average, require all three expected days to be complete. Otherwise, flag or withhold the value rather than letting AVG silently ignore NULL days and average the remainder.

This is a subtle distinction: both zero filling and leaving NULL can mislead if their implications are not stated. Zero assumes no activity; AVG over non-NULL values changes the effective number of observed days.

Preserve enough history before filtering the display

Suppose the dashboard shows only February. A three-day value for February 1 needs January 30 and 31 as well. If the query filters the input to February before applying the window, the first values become partial even though earlier source data exists.

Calculate over the required lookback range, then filter the final display in an outer query. The running-total lesson explains the same distinction between window input and displayed output.

Also decide whether the window uses calendar days, trading days or business days. A seven-business-day average needs a business calendar, not merely a seven-row slice of arbitrary events.

Choose smoothing with the decision in mind

A longer window reduces short-term movement but can delay visibility of a real change. A shorter window reacts faster but may exaggerate normal variation. There is no universally best number of days.

Show the raw series alongside the smoothed one when readers need to see what was averaged away. Do not use a moving average to conceal an outage, a sudden data-definition change or a known source gap.

For ratios such as conversion rate, averaging daily percentages equally may introduce another weighting error. A rolling conversion rate often needs rolling conversions divided by rolling eligible visits, not the average of daily ratios.

Exercise: remove January 6 from the calendar and compare the resulting window. Then restore it but mark the source incomplete. Explain why those are different defects and why neither should silently produce an ordinary three-day average.

NeuraPath's Data Analytics with Generative AI course connects SQL windows, data quality and business reporting. A reliable moving average should make its calendar, completeness assumptions and denominator as clear as its line on the chart.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Sessionize event data with a clear inactivity rule.
• Continue with Build an RFM segmentation table with SQL.