Moving Average in SQL With Window Functions

Why moving averages exist

Daily revenue, daily active users, daily anything — raw daily numbers bounce around from weekday/weekend effects, one-off spikes, and plain noise, which makes a line chart hard to read trend from. A moving average replaces each point with the average of itself and its recent neighbors, which cancels out short-term noise while preserving the underlying trend.

Trailing moving average: the core pattern

The frame clause ROWS BETWEEN N PRECEDING AND CURRENT ROW is what makes this a moving window instead of a flat, whole-table average:

SELECT
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

6 PRECEDING AND CURRENT ROW is 7 rows total (6 before + the current one) — a common off-by-one trap when translating "N-day average" into a frame clause. For a 30-day average, it's 29 PRECEDING.

Worked example: 7-day sales average

Notice June 3rd's spike to 400 barely moves the moving average (260 vs. a raw 400) — that's the smoothing effect in action. By June 8th, the window is fully "warmed up" and averaging exactly 7 rows every time.

Centered moving average

A trailing average lags behind sharp trend changes, since it only looks backward. A centered average looks both directions, which tracks the underlying curve more tightly — at the cost of not being computable for the very newest data, since there's no "future" yet to look forward to:

SELECT
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7d
FROM daily_sales;

What happens at the edges of the data

SQL's window frame doesn't error out when there aren't enough preceding rows — it just shrinks to whatever's available. On day 2 of the dataset, a "7-day" moving average is really an average of 2 rows, not 7:

This is rarely a bug, but it is worth flagging on a chart or in documentation — the first few points of a moving-average line are less smoothed than the rest, and can look artificially close to the raw data.

Per-group moving averages

PARTITION BY keeps each group's window completely separate, exactly as with running totals:

SELECT
  product_id,
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_product_sales;

Each product's moving average restarts independently — product B's window never includes rows from product A, even on the same calendar dates.

ROWS vs. RANGE for moving windows

Just like running totals, moving averages should almost always use ROWS, not the default RANGE. ROWS counts a fixed number of physical rows; RANGE groups tied ORDER BY values together and can quietly include more or fewer rows than intended. For date-based moving averages with one row per day and no duplicate dates, the difference rarely shows up — but it's a silent landmine the moment two rows ever share a timestamp.

Common mistakes

• Off-by-one in the frame size. N PRECEDING AND CURRENT ROW covers N + 1 rows total, not N — a genuine 7-day average needs 6 PRECEDING.
• Forgetting PARTITION BY when separate groups shouldn't blend into one shared moving average.
• Treating a centered average as usable in real time — it can't be computed for the most recent rows since it needs future data that doesn't exist yet.
• Not accounting for gaps in the date series. If some days have no rows at all, N PRECEDING counts physical rows back, not calendar days back — a week with a missing day silently reaches further back in time than expected.
• Ignoring the shrinking window at the start of the data and presenting early points as equally smoothed as later ones.