Moving Averages
SQL · Extra Concepts
Theory
A moving (rolling) average replaces each value with the average of itself and a fixed number of neighbouring rows, which smooths out day-to-day noise and reveals the trend. A 3-day moving average on day 5 is the average of days 3, 4 and 5; on day 6 the window slides forward to days 4, 5 and 6.
In SQL you write it with a window function and an explicit frame: AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). The frame clause says exactly which rows go into each average. At the start of the data there are fewer rows available, so day 1 averages only itself and day 2 averages two days; filter those rows out or show them with a note if a full window matters. ROWS counts physical rows, so if a day is missing from the table the window silently spans more calendar days. Fill gaps with a calendar table first, or use a RANGE frame with an interval in databases that support it, such as PostgreSQL.
Why we use it
Raw daily numbers jump around because of weekends, promotions and random noise, hiding the real direction. A moving average smooths the series so trends and turning points are easy to see.
When to use it
Use it on time series such as daily sales, signups or sensor readings. Choose a window that matches the cycle (7 days for weekly patterns). Use a running total for to-date figures, and a plain GROUP BY average for one value per period.
How it works
- Order rows by date
- Define the frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
- For each row, collect itself and up to 2 earlier rows
- Fewer rows than the window? Yes: Average what exists. No: Average the full window.
- Slide forward one row and repeat
- Smoothed series alongside the original values
Syntax
AVG(column) OVER (ORDER BY col ROWS BETWEEN n PRECEDING AND CURRENT ROW)Example
SELECT name, salary,
AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM employees;Key points
- Common in trend analysis to reduce noise in a metric
- The frame size controls how "smooth" the average is
- First rows have a partial window — filter or flag them
- ROWS counts rows, not days — missing dates stretch the window
- Use a 7-row window for daily data with weekly patterns