What is the difference between ROWS and RANGE in a window frame clause, and when does it matter?

How to think about it

This question is really about understanding that ORDER BY inside a window function doesn’t fully specify the frame — you also need to say how to count rows. Most people write ORDER BY and assume ROWS behavior. The default is actually RANGE, and the difference only bites you when two rows share the same ORDER BY value — which happens constantly with dates.

What each keyword means

ROWS counts physical row positions — the frame is always a fixed number of rows, regardless of what values those rows hold.

RANGE counts logical value distance — all rows with the same ORDER BY value as the current row are “peers” and are all included together.

-- ROWS: exactly the current row + 2 physical rows before it (always 3 rows max)
SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- RANGE: all rows whose order_date is within 2 days of the current row's date
-- The number of rows varies depending on how many rows share nearby dates
SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
)

Where they diverge — the same-date problem

Suppose three rows all have order_date = 2024-01-05. With RANGE and UNBOUNDED PRECEDING AND CURRENT ROW, all three are peers — so all three see the same running total (already including all three amounts). The running total doesn’t staircase; it jumps to the same value for all three.

With ROWS, each physical row is independent, so the running total accumulates one row at a time.

Run this to see the divergence directly:

When each is the right tool

Use ROWS when:

• You want a true staircase running total — each physical row contributes once.
• The ORDER BY column is not unique per partition (dates, hours, bucketed values).
• You’re computing moving averages with a fixed look-back count (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Use RANGE when:

• You genuinely want to aggregate all rows within a calendar distance: “sum of everything in the last 7 days relative to this row’s date” — a row count that varies legitimately.
• Using RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for FIRST_VALUE/LAST_VALUE across an entire partition.