Moving averages with window frames in SQL

Introduction

Moving averages help you see trends by smoothing out data over time. Window frames let you control which rows to include in the average.

To find the average sales over the last 3 days for each day in a sales report.

To smooth out stock prices to see general trends without daily ups and downs.

To calculate the average temperature over the past week for each day in weather data.

To analyze average customer ratings over recent reviews in an app.

To monitor average website visits over the last 7 days for each day.

Syntax

SQL

SELECT column1, column2, 
       AVG(target_column) OVER (ORDER BY order_column ROWS BETWEEN frame_start AND frame_end) AS moving_average
FROM table_name;

ORDER BY inside OVER() defines the order of rows for the moving average.

ROWS BETWEEN defines the window frame, like how many rows before and after to include.

Examples

This calculates the average sales for the current day and the two days before it, making a 3-day moving average.

SQL

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_days
FROM sales_data;

This calculates the average sales for the 4 days before yesterday, excluding the current day.

SQL

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS moving_avg_prev_4_days
FROM sales_data;

This calculates the average sales from the first day up to the current day (cumulative average).

SQL

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg
FROM sales_data;

Sample Program

This example creates a simple sales table with 5 days of sales. It then calculates a 3-day moving average for each day, including the current day and the two days before.

SQL

CREATE TABLE sales_data (
  day INT,
  sales INT
);

INSERT INTO sales_data (day, sales) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_days
FROM sales_data
ORDER BY day;

Important Notes

If there are fewer rows than the frame size at the start, the average uses only available rows.

You can use RANGE instead of ROWS for value-based frames, but ROWS is simpler for beginners.

Always include ORDER BY inside the window function to define the sequence.

Summary

Moving averages smooth data by averaging values over a sliding window.

Window frames define which rows to include in the average relative to the current row.

Use ROWS BETWEEN with PRECEDING and CURRENT ROW to control the frame size.