Course contentsShow
← Data Science
Lesson 1017 of 2,14526. Advanced SQLFree lesson

Moving Averages with Window Frames

Compute moving averages using AVG() OVER with preceding and following row specifications.

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.