Window Frames Deep Dive: RANGE vs ROWS and the Patterns That Replace Self-Joins

The first window function everyone learns is ROW_NUMBER(), and the second is a running total. The running total is where people get quietly burned, because the default frame is not what almost anyone assumes it is.

I have watched a finance model report the same cumulative revenue for four different rows on the same day and nobody flagged it for a quarter. The SQL was sum(amount) over (order by order_date). That is not a running total by row. It is a running total by distinct date value, and on a table with multiple orders per day it produces a step function instead of a curve.

The fix is one keyword. Understanding why it is one keyword is what makes the rest of the window function surface area stop being guesswork. Frames are the whole game: which rows the function can see when it evaluates for the current row.

This post is about frames first, then the patterns that let you delete self-joins from your codebase. The basics of ROW_NUMBER, RANK, and LAG are covered in the window functions primer; this is the layer underneath.

What a window actually evaluates

A window function runs after FROM, WHERE, GROUP BY, and HAVING, and before ORDER BY and LIMIT. It does not reduce rows. For each input row, the engine builds a set of visible rows — the frame — and computes the function over that set.

Three clauses control the frame:

• PARTITION BY splits the input into independent groups. No frame ever crosses a partition boundary.
• ORDER BY defines the sequence inside the partition. Without it there is no notion of “preceding” and the frame is the whole partition.
• The frame spec (ROWS, RANGE, or GROUPS) picks the slice of that ordered partition the function sees.

flowchart TD
  rows["Rows after WHERE and GROUP BY"] --> part["PARTITION BY splits groups"]
  part --> sort["ORDER BY sequences each group"]
  sort --> frame{"Frame spec"}
  frame -->|ROWS| phys["Physical offsets: count rows"]
  frame -->|RANGE| logical["Logical: match ORDER BY values"]
  frame -->|GROUPS| peer["Whole peer groups of ties"]
  phys --> calc["Function evaluates over frame"]
  logical --> calc
  peer --> calc

The frame is recomputed for every row. Ties are where ROWS and RANGE part ways.

The rule worth memorizing: ROWS counts rows, RANGE compares values. With ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, the engine takes three physical rows. With RANGE, it takes every row whose ORDER BY value falls inside a value interval, which may be one row or ten thousand.

The default frame is a trap

When you write an ORDER BY inside OVER() and omit the frame, the SQL standard fills in:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Note RANGE, not ROWS. “Current row” under RANGE means “current row and all of its peers” — every row with the same ORDER BY value. So on a table with three orders on 2026-03-01, all three rows get the same cumulative sum, which includes all three amounts.

-- Same partition, three orders on the same day.
select
  order_date,
  amount,
  -- Default frame: RANGE. All same-day rows share one total.
  sum(amount) over (order by order_date)                as running_range,
  -- Explicit ROWS: true row-by-row cumulative.
  sum(amount) over (
    order by order_date
    rows between unbounded preceding and current row
  )                                                     as running_rows
from fct_orders
order by order_date;

-- order_date  amount  running_range  running_rows
-- 2026-03-01     100            600           100
-- 2026-03-01     200            600           300
-- 2026-03-01     300            600           600
-- 2026-03-02      50            650           650

Both columns are correct for some question. running_range answers “cumulative revenue through end of this day.” running_rows answers “cumulative revenue through this order.” The bug is not choosing; the bug is not knowing you chose.

My default in every model: write the frame explicitly whenever the aggregate is order-sensitive. If a reviewer has to remember the standard’s default to check your math, the SQL is not done. The only time I omit it is when the ORDER BY column is unique within the partition, and even then I usually write ROWS to document the intent.

GROUPS is the third option and is rarely needed: GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW means the current peer group plus one whole preceding peer group. It is useful for “this day and the previous day” when days have variable row counts, and it is supported on Postgres 11+, Snowflake, and DuckDB but not BigQuery.

Moving windows and time-based frames

Physical frames handle “last N rows.” Value-based frames handle “last N days,” which is what business people actually ask for.

-- 7-row trailing average: wrong if a day is missing.
select
  metric_date,
  value,
  avg(value) over (
    partition by metric_name
    order by metric_date
    rows between 6 preceding and current row
  ) as ma7_rows
from daily_metrics;

-- 7-day trailing average: correct across gaps.
select
  metric_date,
  value,
  avg(value) over (
    partition by metric_name
    order by metric_date
    range between interval '6 days' preceding and current row
  ) as ma7_days
from daily_metrics;

The ROWS version drifts the moment a metric skips a day — seven rows might span eleven calendar days, and your “weekly” average quietly becomes a week-and-a-half average. The RANGE version stays honest. Postgres, Snowflake, and DuckDB all accept interval offsets; BigQuery restricts RANGE to numeric offsets, so there you convert the date to an integer day number first:

-- BigQuery: numeric RANGE over a day ordinal.
select
  metric_date,
  avg(value) over (
    partition by metric_name
    order by unix_date(metric_date)
    range between 6 preceding and current row
  ) as ma7_days
from daily_metrics;

If you need gap-free output rather than gap-tolerant math, join a date spine before the window. A spine is a deliberate grain change, which is the one kind of fan-out you can name — see the fan-out post for why naming it matters.

Patterns that replace self-joins

Almost every self-join I find in a warehouse is comparing a row to its own neighbors. That is a window function wearing an expensive costume, and the join version usually scans the table twice.

Previous value and delta. The self-join version needs a correlated max on a smaller timestamp. The window version is one line.

select
  device_id,
  reading_ts,
  temperature,
  lag(temperature) over w                       as prev_temp,
  temperature - lag(temperature) over w         as delta,
  -- Seconds since the previous reading, for gap detection.
  datediff('second', lag(reading_ts) over w, reading_ts) as gap_s
from sensor_readings
window w as (partition by device_id order by reading_ts);

Sessionization. Start a new session whenever the gap exceeds 30 minutes. This is a two-step pattern — flag, then cumulative sum the flag — and it is the single highest-value window trick I know.

with flagged as (
  select
    user_id,
    event_ts,
    -- 1 when this event starts a new session, else 0.
    case
      when lag(event_ts) over w is null then 1
      when datediff('minute', lag(event_ts) over w, event_ts) > 30 then 1
      else 0
    end as is_new_session
  from app_events
  window w as (partition by user_id order by event_ts)
),
sessionized as (
  select
    user_id,
    event_ts,
    -- Cumulative sum of the flag becomes a dense session number.
    sum(is_new_session) over (
      partition by user_id
      order by event_ts
      rows between unbounded preceding and current row
    ) as session_seq
  from flagged
)
select user_id, session_seq, min(event_ts) as started_at,
       max(event_ts) as ended_at, count(*) as events
from sessionized
group by user_id, session_seq;

Gaps and islands. The same flag-then-cumulative-sum shape collapses consecutive runs — consecutive active days, contiguous status periods, unbroken subscription spans. Once you see sessionization and islands as the same pattern, you stop writing recursive queries for problems that do not need them.

First and last value per group. Use FIRST_VALUE and LAST_VALUE, but LAST_VALUE needs a full frame or it returns the current row, because the default frame ends at the current row.

select
  order_id,
  customer_id,
  first_value(status) over w  as first_status,
  -- Without the explicit frame this returns the current row's status.
  last_value(status) over (
    partition by customer_id
    order by updated_at
    rows between unbounded preceding and unbounded following
  ) as latest_status
from order_status_history
window w as (partition by customer_id order by updated_at);

Share of total. A window without ORDER BY gives the whole partition, so amount / sum(amount) over (partition by region) computes a share in one pass instead of joining to a grouped aggregate.

Deduplication. ROW_NUMBER plus QUALIFY picks a survivor row and states the tiebreaker in one place. That pattern has enough variants to deserve its own writeup, which is the deduplication strategies post.

Filtering on a window result

WHERE runs before window functions are computed, so you cannot filter on one directly. Two options:

-- Portable: wrap in a subquery or CTE.
select *
from (
  select
    order_id, customer_id, ordered_at,
    row_number() over (
      partition by customer_id order by ordered_at desc
    ) as rn
  from fct_orders
) t
where rn = 1;

-- Snowflake, BigQuery, DuckDB: QUALIFY does it in place.
select order_id, customer_id, ordered_at
from fct_orders
qualify row_number() over (
  partition by customer_id order by ordered_at desc
) = 1;

QUALIFY is not standard SQL and Postgres does not have it. In a dbt project that targets more than one warehouse, the subquery form is the portable choice; inside a Snowflake-only repo I use QUALIFY everywhere because it removes a nesting level and makes the grain claim readable at a glance.

Performance: sorts are the cost

A window function costs a sort per distinct window specification, plus a sequential pass. The optimizer reuses a sort when two functions share the exact same PARTITION BY and ORDER BY, and does not when they differ by a single column.

-- Three distinct specs. Up to three sorts.
select
  sum(amount)  over (partition by customer_id order by ordered_at),
  avg(amount)  over (partition by customer_id order by ordered_at desc),
  count(*)     over (partition by region)
from fct_orders;

-- One named window, reused. One sort.
select
  sum(amount)   over w,
  avg(amount)   over w,
  count(*)      over w
from fct_orders
window w as (partition by customer_id order by ordered_at);

Two practical consequences. First, filter before you window: a WHERE that removes 90% of rows before the sort is worth more than any frame tuning. Second, a hot partition — one key with tens of millions of rows — serializes onto one worker, because a partition cannot be split. When a window job is slow and the query profile shows one node doing all the work, check partition skew before you blame the function.

Also watch the memory shape. UNBOUNDED FOLLOWING frames force the engine to buffer to the end of the partition. On wide rows with large partitions that spills to disk, and spilling is where window queries go from seconds to minutes.

Pitfalls

Omitting the frame on an order-sensitive aggregate. The default is RANGE, and it merges ties. Write ROWS explicitly or verify your ORDER BY column is unique within the partition.

LAST_VALUE without a full frame. It returns the current row and looks plausible on sorted sample data. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or invert the ORDER BY and use FIRST_VALUE.

A non-deterministic tiebreaker in ROW_NUMBER. If two rows tie on updated_at, the survivor changes between runs and your incremental model produces different output from the same input. Add a unique tiebreaker column to the ORDER BY.

ROWS BETWEEN 6 PRECEDING for a seven-day average. Correct only if the series has no gaps. Use a RANGE interval frame or join a date spine.

RANK where you meant ROW_NUMBER. RANK emits ties, so rank() = 1 can return two rows and reintroduce the duplication you were deduplicating. DENSE_RANK has the same property.

Windows over a fanned-out join. The window computes on the duplicated rows, so the running totals are wrong in a way that DISTINCT cannot fix. Fix the grain first — the anti-patterns post covers why that patch fails.

FAQ

When should I use RANGE instead of ROWS?

Use RANGE when the question is expressed in units of the ORDER BY column — “trailing 7 days,” “within 100 dollars,” “through end of day.” Use ROWS when the question is expressed in records — “last 10 readings,” “cumulative by transaction.” If you cannot phrase the question either way, you have not defined the metric yet.

Does PARTITION BY relate to table partitioning?

No, and the shared word causes real confusion. PARTITION BY in a window is a runtime grouping for the sort. Table partitioning is physical storage layout that controls pruning, which is a different lever entirely and lives in the BigQuery partitioning guide.

Can I use a window function inside a WHERE clause?

Not directly. Wrap it in a subquery or CTE and filter outside, or use QUALIFY on Snowflake, BigQuery, and DuckDB. The restriction exists because WHERE is evaluated before windows, so the frame would be undefined.

Are window functions slower than GROUP BY?

For the same aggregate over the same keys, GROUP BY is usually cheaper because it emits fewer rows. Window functions win when you need the aggregate alongside every detail row, since the alternative is a GROUP BY plus a join back — more passes, and a join that can fan out.

Can I nest window functions?

No. sum(row_number() over ()) over () is a syntax error everywhere. Compute the inner one in a CTE and window over the result. The sessionization example above is exactly that: LAG in one CTE, cumulative SUM in the next.

Do all warehouses support named WINDOW clauses?

Postgres, Snowflake, DuckDB, and Redshift do. BigQuery does too, though it is underused there. When it is available, use it — one declaration means one sort and one place to change the spec.

What this means for your pipelines

Window functions are the difference between SQL that describes a result and SQL that describes a procedure. Once frames are explicit, an entire category of self-joins and correlated subqueries disappears from your models, and the ones that remain are doing real relational work.

The discipline is small. Write the frame whenever the aggregate depends on order. Make the ORDER BY deterministic, including a unique tiebreaker. Declare one named window and reuse it so the engine sorts once. State the output grain in a comment above every QUALIFY, because a ROW_NUMBER filter is a grain claim and grain claims deserve tests.

Do that and window-heavy models get faster and more reviewable at the same time. The cost of a window function is a sort you can see in the profile. The cost of the self-join it replaced was a second full scan and a fan-out risk nobody wrote down.