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 a fact most people miss: ORDER BY inside a window doesn’t fully specify the frame — you also have to say how to count rows. Write ORDER BY alone and the engine defaults to RANGE, not ROWS, and the difference bites the moment two rows share an ORDER BY value — which happens constantly with dates.

• ROWS counts physical row positions: a fixed number of rows, whatever values they hold.
• RANGE counts logical value distance: every row with the same ORDER BY value as the current row is a “peer” and is included together.

A worked example — the same-date divergence

Three orders share 2024-01-05. Running totals with ROWS and RANGE agree everywhere except on those tied dates:

SELECT id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
         ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows,
       SUM(amount) OVER (ORDER BY order_date
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_range
FROM orders
ORDER BY order_date, id;

id	order_date	amount	running_rows	running_range
1	2024-01-03	100	100	100
2	2024-01-05	50	150	280
3	2024-01-05	60	210	280
4	2024-01-05	70	280	280
5	2024-01-08	200	480	480

Look at the three Jan-05 rows. running_rows staircases — 150, 210, 280 — adding one physical row at a time. running_range reads 280, 280, 280 — because under RANGE all three same-date rows are peers, so each one already sees the whole day’s total. The “running total” appears to stall. Outside the tie (Jan 3, Jan 8) the two columns agree, which is exactly why the bug hides on data without duplicate keys.

Which to use

• ROWS for a true staircase running total, for any non-unique ORDER BY column (dates, hours, buckets), and for fixed look-back moving averages (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
• RANGE when you genuinely want a value-distance window — “everything within 7 calendar days of this row’s date,” a count that varies legitimately — or RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to give FIRST_VALUE/LAST_VALUE the whole partition.