ROWS vs RANGE Framing
The subtle and heavily tested difference between row-based and value-based frames.
The Distinction Interviewers Probe
Once you can write a running total, the natural follow-up is: "What is the difference between ROWS and RANGE in a window frame?" This is a precise mid-level signal. Many candidates use frames daily without ever noticing the two keywords behave differently.
Both define the set of rows the aggregate sees, but they count that set in fundamentally different ways. Get this right and you stand out.
ROWS Counts Physical Rows
ROWS is positional. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means literally the current row plus the two physical rows directly above it in the ordered sequence.
It does not care whether neighboring rows share the same ORDER BY value. Three rows means three rows, period. This is the framing you almost always want for moving averages and strict running totals.
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rows_sum
FROM sales;