How does the moving average query work?

The CTE reduces the table to one row per day, so a window of seven rows is a window of seven days. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW takes the current day and the six before it. AVG(sales) OVER (...) averages that frame for every row, so the result keeps one output row per day.

What commonly goes wrong with moving average in SQL?

If days are missing, seven rows span more than seven days; fill the gaps first or use a RANGE frame with an interval.

Moving Average in SQL

A moving average smooths a noisy series by averaging each row with the rows just before it, such as a 7-day average of daily sales.

Example query

This example assumes: orders(id, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH daily AS (
  SELECT order_date, SUM(total_amount) AS sales
  FROM orders
  GROUP BY order_date
)
SELECT order_date,
       sales,
       AVG(sales) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_day
FROM daily
ORDER BY order_date;

How it works

The CTE reduces the table to one row per day, so a window of seven rows is a window of seven days.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW takes the current day and the six before it.
AVG(sales) OVER (...) averages that frame for every row, so the result keeps one output row per day.

MySQL, BigQuery, SQL Server and SQLite differences

The frame syntax is the same in MySQL 8+, SQL Server, BigQuery and SQLite 3.25+.
To use a window measured in time rather than rows, PostgreSQL 11+ supports RANGE with an interval, for example RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW.

Common mistakes

If days are missing, seven rows span more than seven days; fill the gaps first or use a RANGE frame with an interval.
The first six rows average fewer than seven days; drop them or show them as partial.
Averaging averages (for example of daily rates) gives the wrong answer; average the underlying values or recompute the ratio.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show daily sales with a 7-day moving average.
What is the 30-day rolling average of new customers per day?