Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

Moving averages with AVG() OVER

A moving average (calculated by averaging a sliding window of rows around each point) smooths out short-term noise so the underlying trend becomes visible.

Example

Smoothing Retail Sales Trends with Moving Averages

SELECT sale_date, revenue, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day_revenue FROM store_sales WHERE channel = 'Online' ORDER BY sale_date

Result:

Step by step

1. Run the query without AVG so you can see what the unfiltered values look like.

   SELECT sale_date, revenue, revenue OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day_revenue FROM store_sales WHERE channel = 'Online' ORDER BY sale_date;

2. AVG returns the mean of the values in a column.

   SELECT sale_date, revenue, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day_revenue FROM store_sales WHERE channel = 'Online' ORDER BY sale_date;

3. AS gives the AVG result a friendly column name so the output reads cleanly.

   SELECT sale_date, revenue, AVG(revenue) AS avg_result OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day_revenue FROM store_sales WHERE channel = 'Online' ORDER BY sale_date;

Practice

Try each one in your head first, then open the answer.

SELECT order_date, AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) FROM orders

SELECT sale_date, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM store_sales

SELECT category, AVG(price) OVER (ORDER BY category ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) FROM products

SELECT hire_date, AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM employees

SELECT review_date, AVG(rating) OVER (ORDER BY review_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM reviews