ROWS vs RANGE vs GROUPS — Frame Modes in Depth

Frame clauses control exactly which rows a window function sees. You've used ROWS BETWEEN ... already — but there are two other frame modes (RANGE and GROUPS) with fundamentally different semantics. Knowing when each applies prevents subtle bugs in running totals, moving averages, and period comparisons.

The three frame modes

function() OVER (
    ORDER BY col
    {ROWS | RANGE | GROUPS} BETWEEN <start> AND <end>
)

ROWS — exact row positions

ROWS counts physical rows before and after the current row, regardless of their values.

WITH daily AS (
    SELECT
        o.created_at::DATE               AS day,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.created_at::DATE
)
SELECT
    day,
    revenue,
    SUM(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rows_3day_sum
FROM daily
ORDER BY day;

2 PRECEDING AND CURRENT ROW = exactly 3 rows: the current row and the 2 rows immediately before it (by position), regardless of the date gap between them.

Use ROWS for: moving averages, running totals, any calculation where exact row counts matter.

RANGE — value-based boundaries

RANGE defines boundaries based on the value of the ORDER BY column, not row position. All rows within the value range are included.

-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (the default when ORDER BY is present)
WITH daily AS (
    SELECT
        o.created_at::DATE               AS day,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.created_at::DATE
)
SELECT
    day,
    revenue,
    SUM(revenue) OVER (
        ORDER BY day
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS range_running_total
FROM daily
ORDER BY day;

When multiple rows share the same day value, RANGE includes all of them in the "current row" boundary. All tied rows show the same running total — the sum through the last tied row.

With numeric offsets, RANGE becomes powerful for time-based windows:

-- Sum revenue for the current day and the 6 days preceding it (by DATE value, not row count)
WITH daily AS (
    SELECT
        o.created_at::DATE               AS day,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.created_at::DATE
)
SELECT
    day,
    revenue,
    SUM(revenue) OVER (
        ORDER BY day
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS true_7day_sum
FROM daily
ORDER BY day;

This is a true 7-calendar-day window — even if some days are missing from the data, the window still spans exactly 7 days. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW would only span 6 data rows (which might be fewer than 7 calendar days if there are gaps).