Moving Average

Calculates average over a moving window.

Source data — Pattern 10: Window Functions

All queries run against the shared employees table (see schema.sql). This pattern introduces a sales table to demonstrate window functions including ranking, navigation, running totals, moving averages and window aggregates.

Moving Average — source table 1

Moving Average — source table 2

What it does

Calculates average over a moving window.

Problem

Calculate a 3-month moving average.

SELECT *,
AVG(sales_amount) OVER(ORDER BY sale_month ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS result
FROM sales;

Result

Moving Average — result 1