Running Totals and Moving Averages
Understand how to compute daily revenue along with running totals and a 7-day rolling average using SQL. Learn to handle missing dates properly by generating a complete calendar date spine, define explicit window frames, and apply policies for missing revenue to produce accurate time-series metrics. This lesson guides you through building defensible queries that reflect real calendar intervals for interview scenarios.
An interviewer asks, “Compute daily revenue and a 7-day rolling average,” from a table daily_revenue(day, revenue). A weak answer writes AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) directly against the available rows. That query computes an average over seven stored rows, not necessarily seven calendar days. If 2024-01-03 and 2024-01-06 have no row, the value reported on 2024-01-08 can reach farther back than one week. A 7-day rolling average answers a calendar-time question only when the query has one row for every calendar day in the range.
The query should return one row per calendar day, including both the specified start and end dates. Before applying a window function, define how to handle dates with no recorded data. Determine whether a missing date represents zero revenue or unavailable revenue data. This distinction affects how missing dates are represented and how revenue metrics are calculated.
This series contains revenue rows for recorded days but no rows for the highlighted gaps. Averaging the previous seven records would silently substitute older days for 2024-01-03 and 2024-01-06: the seven stored rows ending on 2024-01-08 reach back to 2023-12-31, so the “week” spans nine calendar days. A defensible answer states that the rolling window spans seven calendar days and checks that the query produces every date in that interval.
A concise explanation under follow-up is: “I will aggregate to one row per day, build a calendar date spine, then average the current day and six prior calendar days.”
Make cumulative frames explicit
Once daily grain is correct, a running total needs an explicit rule for which earlier rows contribute to each result. A window frame is ...