Window FRAME specification with ROWS BETWEEN

A window frame specification tells an aggregate calculation - one that summarizes a group of rows - exactly which nearby rows to include, rather than defaulting to all rows from the very beginning.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Controlling the window with ROWS BETWEEN

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

Result:

sale_date	revenue	avg_7d
2026-01-01	1200	1200
2026-01-02	980	1090
2026-01-03	1340	1173
2026-01-04	1100	1155

Step by step

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 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM store_sales;

AVG returns the mean of the values in a column.

SELECT sale_date, revenue, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM store_sales;

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 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM store_sales;

Practice

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

1. On orders, compute a rolling 7-row moving average of revenue ordered by order_date. Use an explicit ROWS BETWEEN frame.

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

2. On store_sales, compute a rolling 7-row moving average of revenue ordered by sale_date. Use an explicit ROWS BETWEEN frame.

SELECT *, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS revenue_7day_avg FROM store_sales

3. On products, compute a rolling 7-row moving average of price ordered by product_id. Use an explicit ROWS BETWEEN frame.

SELECT *, AVG(price) OVER (ORDER BY product_id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS price_7day_avg FROM products

4. On employees, compute a rolling 7-row moving average of salary ordered by hire_date. Use an explicit ROWS BETWEEN frame.

SELECT *, AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS salary_7day_avg FROM employees

5. On reviews, compute a rolling 7-row moving average of rating ordered by review_date. Use an explicit ROWS BETWEEN frame.

SELECT *, AVG(rating) OVER (ORDER BY review_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rating_7day_avg FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed