How do you compute a 3-day moving average of daily revenue with a window function?

middleshould knowfreq 52%

answer

a fixed-width window that slides, not one that grows
the default frame is the wrong one here
name the frame in the OVER clause
2 PRECEDING plus the current row is three rows

basics

~20 s

Use AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). For each row the frame holds that row plus the two before it, so the average slides down the ordered rows, one row at a time.

solid answer

~50 s

Write the aggregate with an explicit row frame: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). The ORDER BY fixes the sequence, and the frame limits each row's window to itself and the two rows before it, so the average trails the data instead of accumulating from the beginning. Two behaviours matter in practice. First, the frame counts rows, not calendar days: if a day is missing from the table, the window silently reaches further back in time, so feed the query a gap-free date series or a calendar join when the metric must be per-day. Second, the first rows of each partition have short frames — row 1 averages one value, row 2 averages two — so the series starts off noisy; filter those rows out if a partial average is misleading. PARTITION BY store_id gives an independent moving average per store.

Write the aggregate with an explicit row frame: `AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)`. The `ORDER BY` fixes the sequence, and the frame limits each row's window to itself and the two rows before it, so the average trails the data instead of accumulating from the beginning. Two behaviours matter in practice. First, the frame counts **rows, not calendar days**: if a day is missing from the table, the window silently reaches further back in time, so feed the query a gap-free date series or a calendar join when the metric must be per-day. Second, the first rows of each partition have short frames — row 1 averages one value, row 2 averages two — so the series starts off noisy; filter those rows out if a partial average is misleading. `PARTITION BY store_id` gives an independent moving average per store.

code

sql · 8 lines

sql

SELECT day,
       revenue,
       AVG(revenue) OVER (ORDER BY day
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3,
       COUNT(*)     OVER (ORDER BY day
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_rows
FROM daily_revenue;
-- window_rows < 3 marks the partial windows at the start of the series

go deeper

for a junior

Memorise the shape: AVG(measure) OVER (ORDER BY time_column ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), and be able to say how many rows that frame contains.

for a middle

Explain why the explicit frame is required — without it you get the default cumulative frame and a running average — and get the off-by-one right when converting a window size into a PRECEDING count.

for a senior

Show you have shipped one of these: partial windows at the start of a series, missing dates stretching the window in time, and a calendar spine as the portable fix, plus PARTITION BY so the smoothing never crosses entities.

for a principal

Set the smoothing convention for the organisation — window width, trailing versus centred, how absent days are treated — because two teams with different conventions will publish different numbers for the same metric.

What a moving average is asking for

A moving (rolling, trailing) average smooths a noisy series by replacing each point with the mean of a small neighbourhood around it. In SQL that neighbourhood is a frame: the subset of the ordered partition that the aggregate sees for the current row. A running total uses a frame that starts at the beginning and grows; a moving average uses a frame of fixed width that slides.

sql

SELECT day,
       revenue,
       AVG(revenue) OVER (ORDER BY day
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM daily_revenue;

Reading the frame clause

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW says: start the frame two rows before this one, end it at this one. Three rows, therefore a 3-point trailing average. The generalisation is mechanical — an n-row trailing window is ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW, so a 7-row window is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. The off-by-one is the most common slip: 6 PRECEDING gives seven rows because the current row counts too.

A centred average puts the row in the middle: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING for a 3-point centred mean. Frames may look forward as well as backward, which is fine for analysis but wrong for anything that must not use future data.

The explicit frame is not optional decoration. Without it, AVG(revenue) OVER (ORDER BY day) gets the default frame from the start of the partition to the current row and returns a running average — every value influenced by the whole history. That is a different metric that happens to compile.

Worked example

With revenue 10, 20, 60, 40 on consecutive days:

day 1: frame = {10} → 10
day 2: frame = {10, 20} → 15
day 3: frame = {10, 20, 60} → 30
day 4: frame = {20, 60, 40} → 40

Notice days 1 and 2: the frame is clipped at the partition boundary, so the first values are averages of fewer points. Nothing is NULL and nothing errors — the series simply starts with partial windows. If a dashboard must not show a "3-day average" computed from one day, compute a row number or a windowed COUNT(*) over the same frame and blank out rows where the count is below the window width.

Rows are not days

ROWS counts rows in the ordered partition. It has no idea what a day is. If the table stores only days with activity, then after a two-day outage the "3-day" window for the next row actually spans five calendar days, and the metric quietly changes meaning. Two fixes:

Generate a gap-free calendar (a dates table, or a recursive CTE producing one row per date) and LEFT JOIN the measures onto it, treating missing days as 0. Then one row really is one day.
Use a value-based frame in engines that support RANGE with an interval offset, which defines the window in time rather than in rows. Support for interval-typed RANGE offsets varies between engines, so check your documentation before relying on it.

The calendar-spine approach is the portable one and is what most interviewers want to hear.

Per-group moving averages

Add a partition and the smoothing runs independently inside each group:

sql

SELECT store_id, day, revenue,
       AVG(revenue) OVER (PARTITION BY store_id ORDER BY day
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM store_daily;

Without PARTITION BY, the first days of one store are averaged with the last days of another — a silent, plausible-looking error.

Other aggregates over the same frame

Every aggregate accepts the same frame, so a single pass can produce a rolling sum (SUM), a rolling extreme (MAX/MIN) and a rolling non-null count (COUNT(revenue)) beside the average. Comparing the rolling COUNT(*) with the window width is the standard way to detect the partial windows described above.

Common mistakes

Omitting the frame and shipping a running average labelled "moving average".
Off-by-one: ROWS BETWEEN 7 PRECEDING AND CURRENT ROW is eight rows, not seven.
Forgetting that missing dates stretch the window in time.
Omitting PARTITION BY so the window crosses group boundaries.
Presenting the leading partial-window values as if they were full-width averages.

## What a moving average is asking for A moving (rolling, trailing) average smooths a noisy series by replacing each point with the mean of a small neighbourhood around it. In SQL that neighbourhood is a *frame*: the subset of the ordered partition that the aggregate sees for the current row. A running total uses a frame that starts at the beginning and grows; a moving average uses a frame of fixed width that slides. ```sql SELECT day, revenue, AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3 FROM daily_revenue; ``` ## Reading the frame clause `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` says: start the frame two rows before this one, end it at this one. Three rows, therefore a 3-point trailing average. The generalisation is mechanical — an *n*-row trailing window is `ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW`, so a 7-row window is `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`. The off-by-one is the most common slip: `6 PRECEDING` gives seven rows because the current row counts too. A *centred* average puts the row in the middle: `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` for a 3-point centred mean. Frames may look forward as well as backward, which is fine for analysis but wrong for anything that must not use future data. The explicit frame is not optional decoration. Without it, `AVG(revenue) OVER (ORDER BY day)` gets the default frame from the start of the partition to the current row and returns a *running* average — every value influenced by the whole history. That is a different metric that happens to compile. ## Worked example With revenue 10, 20, 60, 40 on consecutive days: - day 1: frame = {10} → 10 - day 2: frame = {10, 20} → 15 - day 3: frame = {10, 20, 60} → 30 - day 4: frame = {20, 60, 40} → 40 Notice days 1 and 2: the frame is clipped at the partition boundary, so the first values are averages of fewer points. Nothing is NULL and nothing errors — the series simply starts with partial windows. If a dashboard must not show a "3-day average" computed from one day, compute a row number or a windowed `COUNT(*)` over the same frame and blank out rows where the count is below the window width. ## Rows are not days `ROWS` counts rows in the ordered partition. It has no idea what a day is. If the table stores only days with activity, then after a two-day outage the "3-day" window for the next row actually spans five calendar days, and the metric quietly changes meaning. Two fixes: 1. Generate a gap-free calendar (a dates table, or a recursive CTE producing one row per date) and LEFT JOIN the measures onto it, treating missing days as 0. Then one row really is one day. 2. Use a value-based frame in engines that support `RANGE` with an interval offset, which defines the window in time rather than in rows. Support for interval-typed `RANGE` offsets varies between engines, so check your documentation before relying on it. The calendar-spine approach is the portable one and is what most interviewers want to hear. ## Per-group moving averages Add a partition and the smoothing runs independently inside each group: ```sql SELECT store_id, day, revenue, AVG(revenue) OVER (PARTITION BY store_id ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7 FROM store_daily; ``` Without `PARTITION BY`, the first days of one store are averaged with the last days of another — a silent, plausible-looking error. ## Other aggregates over the same frame Every aggregate accepts the same frame, so a single pass can produce a rolling sum (`SUM`), a rolling extreme (`MAX`/`MIN`) and a rolling non-null count (`COUNT(revenue)`) beside the average. Comparing the rolling `COUNT(*)` with the window width is the standard way to detect the partial windows described above. ## Common mistakes - Omitting the frame and shipping a running average labelled "moving average". - Off-by-one: `ROWS BETWEEN 7 PRECEDING AND CURRENT ROW` is eight rows, not seven. - Forgetting that missing dates stretch the window in time. - Omitting `PARTITION BY` so the window crosses group boundaries. - Presenting the leading partial-window values as if they were full-width averages.

What do you get if you leave the frame out and write AVG(revenue) OVER (ORDER BY day)?
A running average, not a moving one. With an ORDER BY and no explicit frame the window runs from the first row of the partition to the current row, so every value is influenced by the entire history and the series stops responding to recent changes. The query compiles, which is what makes the mistake dangerous.
Your table has no rows for days with zero sales. How does that affect a 7-row moving average?
The window counts rows, so seven rows may span far more than seven days, and the average is taken over a longer, sparser period than the label claims. Build a gap-free date series and LEFT JOIN the measures onto it, treating missing days as 0, so one row equals one day again.
How do you write a centred 3-day average instead of a trailing one?
Use ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, which puts the current row in the middle of the frame. It smooths without lag, but it consumes future rows, so it is unsuitable for any metric that must be computable in real time or must not peek ahead.

It is a three-day window you drag along a calendar: as it moves one day forward it picks up the new day and drops the day that fell off the back.

saying these in an interview costs you the question

Omits the frame and calls a running average a moving average
Says ROWS BETWEEN 3 PRECEDING AND CURRENT ROW is a 3-row window
Assumes ROWS counts calendar days regardless of gaps
Ignores that the first rows have partial windows
Drops PARTITION BY so the window spans several stores

How do you compute a 3-day moving average of daily revenue with a window function?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What a moving average is asking for

Reading the frame clause

Worked example

Rows are not days

Per-group moving averages

Other aggregates over the same frame

Common mistakes

▾common follow-ups3 questions

▾analogy~10 s

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics4 topics

What a moving average is asking for

Reading the frame clause

Worked example

Rows are not days

Per-group moving averages

Other aggregates over the same frame

Common mistakes

answer

go deeper

context