sqlindex

Window functions

Moving average (rolling window) in SQL

2 min · updated June 16, 2026

A moving (rolling) average is AVG over a sliding frame of rows. Same syntax across all six dialects (MySQL 8+, SQLite 3.25+ for window support).

-- 7-row moving average: this row plus the 6 before it
SELECT
  d,
  amount,
  AVG(amount) OVER (ORDER BY d
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM sales
ORDER BY d;

Per group, add PARTITION BY:

SELECT
  symbol, d, price,
  AVG(price) OVER (PARTITION BY symbol ORDER BY d
                   ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma30
FROM prices;

ROWS counts rows, not days. ROWS BETWEEN 6 PRECEDING averages the last 7 rows, which is only “7 days” if there’s exactly one row per day with no gaps. For a true calendar window that ignores missing days, use a RANGE frame with an interval — RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW (PostgreSQL, Snowflake, DuckDB; BigQuery uses a numeric RANGE over a unix timestamp). MySQL and SQLite support only numeric RANGE, so fill gaps first (see the generate-series recipe).

← All recipes