Moving Averages with Window Frames
What you'll learn: How to calculate moving (rolling) averages by combining AVG() with OVER and frame specifications that look backward and forward.
The Core Idea
A moving average smooths out fluctuations in data by averaging values within a sliding "window" of rows. Instead of averaging an entire column, you average just the current row and its neighbors—like looking at a week's worth of daily temperatures to understand the trend without obsessing over a single cold Tuesday.
You build this using the AVG() aggregate function with the OVER clause, specifying exactly which rows to include using ROWS BETWEEN frame syntax.
Syntax and Examples
The basic pattern:
AVG(column_name) OVER (
ORDER BY date_column
ROWS BETWEEN n PRECEDING AND m FOLLOWING
)
Common patterns:
-- 3-day moving average (current + 2 prior days)
AVG(sales) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
-- Centered 5-day moving average (2 before, current, 2 after)
AVG(temperature) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
)
-- Forward-looking average (current + next 3 rows)
AVG(stock_price) OVER (
ORDER BY trade_date
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING
)
How It Works
As the query engine processes each row, it identifies which neighboring rows fall within your specified frame, calculates their average, and assigns that value to the current row. The frame "slides" with each row, always maintaining the same relative position.
This is incredibly useful for time-series analysis, trend detection, and reducing noise in fluctuating metrics.
Key Takeaway: Moving averages with AVG() OVER (ORDER BY ... ROWS BETWEEN ... AND ...) let you calculate rolling averages across adjacent rows, perfect for smoothing trends and identifying patterns in sequential data.