SQL › Running Totals and Moving Averages

Window Frames: ROWS BETWEEN and RANGE BETWEEN

The frame is the part of the window a function like SUM or AVG uses for the current row. Set it with ROWS BETWEEN (counting rows) or RANGE BETWEEN (comparing values).

Syntax

Order of execution: FROM → WHERE → GROUP BY → window functions (each row's frame) → SELECT → ORDER BY

Code
SELECT column,
       SUM(column) OVER (ORDER BY sort_column
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum,
       SUM(column) OVER (ORDER BY date_column
                         RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS last_7_days
FROM table_name;

RANGE with a distance such as 6 days works in PostgreSQL, MySQL (INTERVAL 6 DAY) and Oracle (INTERVAL '6' DAY). SQL Server allows RANGE only with UNBOUNDED and CURRENT ROW.

Examples

Running total

The default frame with ORDER BY adds everything up to the current row.

Code
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_revenue
FROM v_monthly_sales
WHERE month >= DATE '2024-01-01' AND month < DATE '2025-01-01'
ORDER BY month;

A moving window of rows

Each row adds itself and the row before: a two-month moving total.

Code
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS two_month_total
FROM v_monthly_sales
WHERE month >= DATE '2024-01-01' AND month < DATE '2025-01-01'
ORDER BY month;

Ties: ROWS vs RANGE

Several orders on one date sort equally. The default RANGE frame adds all of that date's orders at once; ROWS adds one row at a time.

Code
SELECT order_date, order_id,
       COUNT(*) OVER (ORDER BY order_date) AS by_range,
       COUNT(*) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows
FROM orders
WHERE order_date BETWEEN DATE '2025-09-10' AND DATE '2025-09-14'
ORDER BY order_date, order_id;

A range of dates

RANGE with an interval covers a span of time, however many rows fall in it.

Code
SELECT order_date, order_id, total_amount,
       SUM(total_amount) OVER (ORDER BY order_date
                               RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS last_7_days
FROM orders
WHERE customer_id = 3
ORDER BY order_date, order_id;

At the edges

Near the start there are fewer rows before, so the frame is smaller.

Code
SELECT month, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS centred_avg
FROM v_monthly_sales
WHERE month >= DATE '2024-01-01' AND month < DATE '2024-07-01'
ORDER BY month;

Remember

• With ORDER BY and no frame, the frame is a running one that includes every row tied with the current row.
• ROWS counts rows; RANGE compares values, so tied rows and missing dates behave differently.
• RANGE with an interval needs a single ORDER BY column of a date or number type.
• Frames shrink at the edges of the partition; the first rows have fewer rows before them.
• Frames matter for aggregates, FIRST_VALUE, LAST_VALUE and NTH_VALUE; ranking functions, LAG and LEAD ignore them.

Practice

1. Running Revenue for 2025 (Medium)
2. Three-Month Moving Average (Medium)
3. Revenue in the Last 30 Days (Hard)