{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"moving-averages","title":"Moving averages: seven rows is not seven days","url":"https://addaly.com/learn/data-and-sql/moving-averages","position":39,"of":66,"minutes":8,"answer":"A ROWS frame counts rows, so a seven-row average over a table with missing days is not a seven-day average; join to a spine or use a RANGE frame measured in time.","keyIdea":"A ROWS frame counts rows, so a seven-row average over a table with missing days is not a seven-day average; join to a spine or use a RANGE frame measured in time, and mark the first partial windows."},"body":{"format":"markdown","text":"## Smoothing a noisy line\n\nDaily revenue jumps around. Weekends dip, a promotion spikes, a bank holiday empties the chart. A seven-day moving average replaces each day's figure with the mean of that day and the six before it, and the weekly rhythm disappears, leaving the trend.\n\n```sql\nSELECT day, revenue,\n       avg(revenue) OVER (ORDER BY day\n                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7\nFROM daily_revenue;\n```\n\nThe frame is the whole idea: instead of running from the start of the partition, it runs from six rows back to here. `ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING` is a centred seven-day window, which has no lag against the trend but cannot be computed for the most recent three days. `ROWS BETWEEN 29 PRECEDING AND CURRENT ROW` is the 30-day version.\n\n## The first six rows\n\nOn the first day of the series the frame contains one row, on the second two, and so on. `avg()` divides by the rows it has, so the first six values are averages of shorter windows and are systematically noisier. Either drop them, mark them, or report the count alongside:\n\n```sql\ncount(*) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS days_in_window\n```\n\nand filter `WHERE days_in_window = 7` in an outer layer. A chart that starts with a partial window shows a false step on day seven, when the average suddenly has its full complement of rows.\n\n> Figure: Fourteen days of revenue, and the seven-day average over it. Line chart of Revenue (thousands) against Day of the month: Seven-day moving average (7, 55.4) (8, 55.6) (9, 56) (10, 55.9) (11, 56.1) (12, 56.6) (13, 56.9) (14, 57.1); Daily revenue (1, 62) (2, 58) (3, 60) (4, 64) (5, 71) (6, 40) (7, 33) (8, 63) (9, 61) (10, 59) (11, 66) (12, 74) (13, 42) (14, 35). The weekend dips on days 6, 7, 13 and 14 disappear into the average, leaving a trend that barely moves. The smoothed line starts on day seven, because the first six frames hold fewer than seven rows; drop them, mark them, or report the row count beside them.\n\n## Seven rows is only seven days if every day is there\n\nA `ROWS` frame counts rows. If the table has no row for a day with no revenue, \"6 PRECEDING\" reaches back further than six days, and the moving average is over whatever seven trading days happened to exist. The line looks smooth and means something different at each point. There are two fixes.\n\nThe first is the spine from the earlier module: generate every date, LEFT JOIN the revenue to it, and coalesce missing days to zero (if zero is what a missing day means) before the window. The second is a `RANGE` frame measured in time rather than rows, which Postgres supports from version 11 and DuckDB supports:\n\n```sql\navg(revenue) OVER (ORDER BY day\n                   RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW)\n```\n\nThis frame includes every row whose `day` is within six days of the current one, however many rows that is. It is honest about gaps, and it changes the meaning slightly: a missing day now contributes nothing rather than a zero, so the average is over the days that traded. Decide which meaning you want, because the two charts will differ exactly where the business was closed.\n\n## A moving average lags the truth\n\nAveraging the last seven days means the line responds to a change about three and a half days after it happened. A trailing 30-day average responds two weeks late. That is the price of smoothing, and it is why a moving average is a bad early-warning signal and a good after-the-fact description. When you need to react quickly, plot the raw series beside the smoothed one, or use a shorter window and accept the noise.\n\n## Moving sums and moving distinct counts\n\nThe same frame works for `sum()`, and \"revenue in the trailing 30 days\" is one of the most useful columns in any daily table. `count(DISTINCT customer_id)` over a moving frame is not supported, for the reason the running-totals lesson gave: a sliding distinct count cannot be updated one row at a time. For \"customers active in the trailing 30 days\" the honest query is a self-join from each day to the orders within its window, or a daily table of first-and-last-active dates.\n\n## Weighted and exponential\n\nA simple moving average weights each of the seven days equally. An exponentially weighted average gives recent days more weight and never fully forgets old ones, and it cannot be written as a single window frame, because each value depends on the previous smoothed value. A recursive CTE can do it and is unpleasant; the free path is to pull the daily series into Python and call `pandas.Series.ewm(span=7).mean()`, or to use LibreOffice Calc with a column formula. Know that the option exists, and reach for SQL's plain moving average first, because it is the one a reader can check by hand.\n\n## The habit\n\nState three things for every moving average: the window length, whether it counts rows or days, and what happens on days with no row. If the chart's caption cannot say all three, the smoothing has hidden something rather than revealed it."},"check":{"question":"A shop closes on Sundays and its `daily_sales` table has no row for those days. Its \"7-day moving average\" uses `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`. What is each value actually an average of?","options":[{"text":"The last seven calendar days, with Sundays treated as zero, because the window's ORDER BY on `day` fills the missing date with a NULL row that averages as zero.","correct":false,"tempting":"Windows do not invent rows for missing dates, and even if a NULL row existed, avg() skips NULLs rather than counting them as zero."},{"text":"The last seven trading days, which span eight calendar days whenever a Sunday falls inside the window.","correct":true},{"text":"Six days, because `6 PRECEDING` excludes the current row and the Sunday gap removes one more.","correct":false,"tempting":"The frame is 6 PRECEDING AND CURRENT ROW, so it holds seven rows; the gap changes which dates those rows cover, not how many there are."},{"text":"The last seven calendar days, because the window measures the distance in the ORDER BY column's units when that column is a date.","correct":false,"tempting":"Describes a RANGE frame with an interval; ROWS counts physical rows regardless of the ordering column's type."}]},"prev":{"slug":"lag-lead-and-change","title":"LAG and LEAD: the previous row, and the change since it","url":"https://addaly.com/learn/data-and-sql/lag-lead-and-change"},"next":{"slug":"gaps-and-islands","title":"Streaks and sessions: gaps and islands","url":"https://addaly.com/learn/data-and-sql/gaps-and-islands"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Moving averages: seven rows is not seven days', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/moving-averages (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Moving averages: seven rows is not seven days","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"A ROWS frame counts rows, so a seven-row average over a table with missing days is not a seven-day average; join to a spine or use a RANGE frame measured in time, and mark the first partial windows.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"line","title":"Fourteen days of revenue, and the seven-day average over it","series":[{"label":"Seven-day moving average","points":[[7,55.4],[8,55.6],[9,56],[10,55.9],[11,56.1],[12,56.6],[13,56.9],[14,57.1]]},{"label":"Daily revenue","points":[[1,62],[2,58],[3,60],[4,64],[5,71],[6,40],[7,33],[8,63],[9,61],[10,59],[11,66],[12,74],[13,42],[14,35]]}],"xLabel":"Day of the month","yLabel":"Revenue (thousands)","caption":"The weekend dips on days 6, 7, 13 and 14 disappear into the average, leaving a trend that barely moves. The smoothed line starts on day seven, because the first six frames hold fewer than seven rows; drop them, mark them, or report the row count beside them."},"alt":"Figure: Fourteen days of revenue, and the seven-day average over it. Line chart of Revenue (thousands) against Day of the month: Seven-day moving average (7, 55.4) (8, 55.6) (9, 56) (10, 55.9) (11, 56.1) (12, 56.6) (13, 56.9) (14, 57.1); Daily revenue (1, 62) (2, 58) (3, 60) (4, 64) (5, 71) (6, 40) (7, 33) (8, 63) (9, 61) (10, 59) (11, 66) (12, 74) (13, 42) (14, 35). The weekend dips on days 6, 7, 13 and 14 disappear into the average, leaving a trend that barely moves. The smoothed line starts on day seven, because the first six frames hold fewer than seven rows; drop them, mark them, or report the row count beside them."}},"blocks":[{"t":"h2","id":"smoothing-a-noisy-line","runs":[{"text":"Smoothing a noisy line"}]},{"t":"p","runs":[{"text":"Daily revenue jumps around. Weekends dip, a promotion spikes, a bank holiday empties the chart. A seven-day moving average replaces each day’s figure with the mean of that day and the six before it, and the weekly rhythm disappears, leaving the trend."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT day, revenue,\n       avg(revenue) OVER (ORDER BY day\n                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7\nFROM daily_revenue;"},{"t":"p","runs":[{"text":"The frame is the whole idea: instead of running from the start of the partition, it runs from six rows back to here. "},{"text":"ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING","code":true},{"text":" is a centred seven-day window, which has no lag against the trend but cannot be computed for the most recent three days. "},{"text":"ROWS BETWEEN 29 PRECEDING AND CURRENT ROW","code":true},{"text":" is the 30-day version."}]},{"t":"h2","id":"the-first-six-rows","runs":[{"text":"The first six rows"}]},{"t":"p","runs":[{"text":"On the first day of the series the frame contains one row, on the second two, and so on. "},{"text":"avg()","code":true},{"text":" divides by the rows it has, so the first six values are averages of shorter windows and are systematically noisier. Either drop them, mark them, or report the count alongside:"}]},{"t":"code","lang":"sql","label":"SQL","text":"count(*) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS days_in_window"},{"t":"p","runs":[{"text":"and filter "},{"text":"WHERE days_in_window = 7","code":true},{"text":" in an outer layer. A chart that starts with a partial window shows a false step on day seven, when the average suddenly has its full complement of rows."}]},{"t":"h2","id":"seven-rows-is-only-seven-days-if-every-day-is-there","runs":[{"text":"Seven rows is only seven days if every day is there"}]},{"t":"p","runs":[{"text":"A "},{"text":"ROWS","code":true},{"text":" frame counts rows. If the table has no row for a day with no revenue, “6 PRECEDING” reaches back further than six days, and the moving average is over whatever seven trading days happened to exist. The line looks smooth and means something different at each point. There are two fixes."}]},{"t":"p","runs":[{"text":"The first is the spine from the earlier module: generate every date, LEFT JOIN the revenue to it, and coalesce missing days to zero (if zero is what a missing day means) before the window. The second is a "},{"text":"RANGE","code":true},{"text":" frame measured in time rather than rows, which Postgres supports from version 11 and DuckDB supports:"}]},{"t":"code","lang":"sql","label":"SQL","text":"avg(revenue) OVER (ORDER BY day\n                   RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW)"},{"t":"p","runs":[{"text":"This frame includes every row whose "},{"text":"day","code":true},{"text":" is within six days of the current one, however many rows that is. It is honest about gaps, and it changes the meaning slightly: a missing day now contributes nothing rather than a zero, so the average is over the days that traded. Decide which meaning you want, because the two charts will differ exactly where the business was closed."}]},{"t":"h2","id":"a-moving-average-lags-the-truth","runs":[{"text":"A moving average lags the truth"}]},{"t":"p","runs":[{"text":"Averaging the last seven days means the line responds to a change about three and a half days after it happened. A trailing 30-day average responds two weeks late. That is the price of smoothing, and it is why a moving average is a bad early-warning signal and a good after-the-fact description. When you need to react quickly, plot the raw series beside the smoothed one, or use a shorter window and accept the noise."}]},{"t":"h2","id":"moving-sums-and-moving-distinct-counts","runs":[{"text":"Moving sums and moving distinct counts"}]},{"t":"p","runs":[{"text":"The same frame works for "},{"text":"sum()","code":true},{"text":", and “revenue in the trailing 30 days” is one of the most useful columns in any daily table. "},{"text":"count(DISTINCT customer_id)","code":true},{"text":" over a moving frame is not supported, for the reason the running-totals lesson gave: a sliding distinct count cannot be updated one row at a time. For “customers active in the trailing 30 days” the honest query is a self-join from each day to the orders within its window, or a daily table of first-and-last-active dates."}]},{"t":"h2","id":"weighted-and-exponential","runs":[{"text":"Weighted and exponential"}]},{"t":"p","runs":[{"text":"A simple moving average weights each of the seven days equally. An exponentially weighted average gives recent days more weight and never fully forgets old ones, and it cannot be written as a single window frame, because each value depends on the previous smoothed value. A recursive CTE can do it and is unpleasant; the free path is to pull the daily series into Python and call "},{"text":"pandas.Series.ewm(span=7).mean()","code":true},{"text":", or to use LibreOffice Calc with a column formula. Know that the option exists, and reach for SQL’s plain moving average first, because it is the one a reader can check by hand."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"State three things for every moving average: the window length, whether it counts rows or days, and what happens on days with no row. If the chart’s caption cannot say all three, the smoothing has hidden something rather than revealed it."}]}],"check":{"question":[{"text":"A shop closes on Sundays and its "},{"text":"daily_sales","code":true},{"text":" table has no row for those days. Its “7-day moving average” uses "},{"text":"ROWS BETWEEN 6 PRECEDING AND CURRENT ROW","code":true},{"text":". What is each value actually an average of?"}],"options":[{"text":[{"text":"The last seven calendar days, with Sundays treated as zero, because the window’s ORDER BY on "},{"text":"day","code":true},{"text":" fills the missing date with a NULL row that averages as zero."}],"correct":false,"tempting":[{"text":"Windows do not invent rows for missing dates, and even if a NULL row existed, avg() skips NULLs rather than counting them as zero."}]},{"text":[{"text":"The last seven trading days, which span eight calendar days whenever a Sunday falls inside the window."}],"correct":true,"tempting":null},{"text":[{"text":"Six days, because "},{"text":"6 PRECEDING","code":true},{"text":" excludes the current row and the Sunday gap removes one more."}],"correct":false,"tempting":[{"text":"The frame is 6 PRECEDING AND CURRENT ROW, so it holds seven rows; the gap changes which dates those rows cover, not how many there are."}]},{"text":[{"text":"The last seven calendar days, because the window measures the distance in the ORDER BY column’s units when that column is a date."}],"correct":false,"tempting":[{"text":"Describes a RANGE frame with an interval; ROWS counts physical rows regardless of the ordering column’s type."}]}],"order":[3,0,2,1],"why":null},"prev":{"slug":"lag-lead-and-change","title":"LAG and LEAD: the previous row, and the change since it","minutes":9},"next":{"slug":"gaps-and-islands","title":"Streaks and sessions: gaps and islands","minutes":9}}}