SQL Window Frames: ROWS/RANGE, Running Totals, and the LAST_VALUE Trap

You have probably already met window functions: SUM() OVER (...), ROW_NUMBER(), and friends. But the moment you reach for running totals and moving averages, a third component of a window shows up that many people skip: the frame. The frame decides exactly which rows inside the partition participate in the calculation for the current row. Misunderstanding it produces quiet, non-obvious bugs — most famously a LAST_VALUE that stubbornly returns the wrong value. Let's work through it on an orders schema.

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    created_at  date   NOT NULL,
    amount      numeric(10,2) NOT NULL
);

The anatomy of a window

A full window definition has three parts: PARTITION BY (which groups to split into), ORDER BY (how to sort inside each group), and the frame (ROWS/RANGE/GROUPS BETWEEN ...). The frame defines a range of rows relative to the current one.

SELECT
    id,
    created_at,
    amount,
    SUM(amount) OVER (
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- the frame
    ) AS running_total
FROM orders;

The key frame bounds:

• UNBOUNDED PRECEDING — from the start of the partition;
• N PRECEDING / N FOLLOWING — N rows back/forward;
• CURRENT ROW — the current row;
• UNBOUNDED FOLLOWING — to the end of the partition.

Important: if you write an ORDER BY but omit the frame, the engine supplies one for you — and it is almost never the one you expect. More on that below.

Running total: ROWS

The classic case is a cumulative sum of orders by date. A frame from the start to the current row does exactly this:

SELECT
    created_at,
    amount,
    SUM(amount) OVER (
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY created_at, id;

Note the id in ORDER BY — it makes the ordering deterministic. If two rows share the same created_at, the order between them is undefined without a tie-breaker, and the result can shift between runs.

Want a running total per customer? Add PARTITION BY:

SELECT
    customer_id,
    created_at,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_running_total
FROM orders;

Moving average: a fixed-width window

A 3-row moving average (current row plus the two before it) is the frame 2 PRECEDING AND CURRENT ROW:

SELECT
    created_at,
    amount,
    AVG(amount) OVER (
        ORDER BY created_at, id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

Want a centered average (one row on each side)? Use BETWEEN 1 PRECEDING AND 1 FOLLOWING.

Gotcha: at the start of a partition the window is "under-filled" — the first row sees only one record in its frame, the second sees two. AVG handles this fine (it divides by the actual row count), but if you want an honest "full-window-only" average, filter on COUNT(*) OVER (...) and drop the incomplete frames.

ROWS vs RANGE — they are different beasts

This is the big one. ROWS counts physical rows. RANGE works on the value of the ORDER BY column: every row sharing the current row's value (its peers) falls into the frame.

-- Two orders on the same day: created_at = '2026-01-10'
SELECT
    created_at,
    amount,
    SUM(amount) OVER (ORDER BY created_at
        ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
    SUM(amount) OVER (ORDER BY created_at
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range
FROM orders;

For the two rows on 2026-01-10, by_rows gives different values (rows are visited one at a time), while by_range gives the same value for both, because they are date peers and collapse into a single step. This is a common source of "weird" duplicate steps in a running total.

PostgreSQL also supports value-based ranges: RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW is a true "rolling 7 calendar days" window, even on days with no orders. There is also GROUPS, a frame counted in units of peer groups.

Engine differences: MySQL supports ROWS and RANGE (since 8.0) but not GROUPS, nor RANGE with INTERVAL in the same shape. ClickHouse has window functions, but interval-based RANGE frames are limited — for time-based rolling windows people usually fall back to ROWS or array helpers like groupArray. Always check the docs for your exact version.

The LAST_VALUE trap and the default frame

Here is the sneakiest part. When an ORDER BY is present but no frame is given, the SQL standard default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

So the window only reaches up to the current row, not to the end of the partition. For FIRST_VALUE this is invisible (the first row is always in frame), but LAST_VALUE breaks:

-- BUG: returns the amount of the CURRENT row, not the last one
SELECT
    created_at,
    amount,
    LAST_VALUE(amount) OVER (ORDER BY created_at, id) AS wrong_last
FROM orders;

The "last value" by default is the last row in the frame, and the frame ends at the current row. You get the current order, not the last one. The fix is an explicit frame to the end of the partition:

SELECT
    created_at,
    amount,
    LAST_VALUE(amount) OVER (
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS correct_last
FROM orders;

An alternative that avoids fiddling with frames: FIRST_VALUE with reversed sorting, or MAX(...) OVER (PARTITION BY ...) with no ORDER BY (then the frame covers the whole partition).

Remember three things: always add a tie-breaker to ORDER BY; the default frame is RANGE ... CURRENT ROW, not "the whole partition"; and for LAST_VALUE you almost always want to spell out ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.