Home/Learn/SQL/Running Totals & Moving Averages

Running Totals & Moving Averages

Advanced

Window Functions

Frame clauses (ROWS BETWEEN / RANGE BETWEEN) control which rows contribute to each window computation, enabling running sums, moving averages, and partition-resetting accumulators.

Overview

The frame clause is the most precise part of an OVER() window definition. ROWS BETWEEN counts rows physically, making it predictable for moving averages. RANGE BETWEEN counts rows by value range and can include or exclude tied values unexpectedly. A running total uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (or relies on the default when ORDER BY is present). A 7-day moving average uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Resetting a running total per partition requires PARTITION BY — the accumulator restarts automatically at each partition boundary.

Cumulative Revenue by Month

A running total with ORDER BY and the default frame accumulates from the first row in the partition to the current row. Make the frame explicit with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for clarity and to avoid RANGE tie issues.

SQL — cumulative monthly revenue (running total)

-- Monthly revenue and cumulative revenue year-to-date
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount)                      AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM monthly
ORDER BY month;

7-Day Moving Average of Daily Orders

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW captures exactly 7 rows (today + 6 prior days). This avoids tie complications that RANGE BETWEEN would introduce when multiple rows share the same date.

SQL — 7-day moving average with ROWS frame

-- Daily order count and 7-day moving average
WITH daily_orders AS (
    SELECT
        created_at::date AS order_date,
        COUNT(*)          AS order_count,
        SUM(amount)       AS daily_revenue
    FROM orders
    GROUP BY 1
)
SELECT
    order_date,
    order_count,
    daily_revenue,
    ROUND(
        AVG(order_count) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_7d_orders,
    ROUND(
        AVG(daily_revenue) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_7d_revenue
FROM daily_orders
ORDER BY order_date;

Running Total that Resets Per User (ROWS vs RANGE difference)

PARTITION BY in the window resets the accumulator at each partition boundary automatically. The ROWS vs RANGE difference matters when tied ORDER BY values exist — ROWS is almost always the safer choice for running totals.

SQL — per-user running total with ROWS vs RANGE note

-- Running spend total that resets per user
SELECT
    user_id,
    id         AS order_id,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS user_running_total
FROM orders
ORDER BY user_id, created_at;

-- ROWS vs RANGE difference demo (when two orders have the same created_at):
-- ROWS BETWEEN ... CURRENT ROW  → includes only current physical row
-- RANGE BETWEEN ... CURRENT ROW → includes ALL rows with the same created_at value
-- For running totals prefer ROWS to avoid unexpected jumps on ties

Key Points to Remember

1ROWS BETWEEN counts physical rows; RANGE BETWEEN counts rows whose ORDER BY value falls within a range — use ROWS for predictable moving windows.
2The default frame when ORDER BY is present inside OVER() is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — make it explicit to avoid surprises.
3PARTITION BY resets the running total automatically at each group boundary; no extra logic needed.
4A 7-day moving average uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (7 rows total including current).
5ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING spans the entire partition — used for LAST_VALUE and grand totals.
6Running totals earlier in the partition will have fewer rows contributing, which is by design and matches the cumulative pattern.

Interview Questions

What is the difference between ROWS BETWEEN and RANGE BETWEEN in a window frame?

HardGoogle

Write a query to compute a 7-day moving average of daily revenue.

MediumNetflix

How do you reset a running total at the start of each user's data?

MediumAmazon

What is the default window frame when ORDER BY is specified inside OVER(), and why can it cause issues?

HardMicrosoft

Ask Aria about Running Totals & Moving Averages

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

NTILE

Window Functions vs GROUP BY