Frame clauses: ROWS vs RANGE, the setting that decides what a window function sees
Every window function looks at a 'frame', a slice of nearby rows. ROWS counts physical rows; RANGE counts by value. They usually agree, until there are duplicate values.
Every window function looks at a "frame": the slice of nearby rows it's allowed to see. You've
already used frames without naming them (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Here's what's
actually going on, and the one case where it surprises people.
🎯 Explain Like I'm Hired
ROWScounts physical rows: "the 2 rows before this one," full stop, no matter what's in them.RANGEcounts by value: "every row whose date falls within this span of the current row's date," which can include more or fewer rows thanROWSwould, if some rows share the same value. Example: if three orders happened on the exact same day,ROWS BETWEEN 1 PRECEDING AND CURRENT ROWlooks back exactly 1 physical row, butRANGEwith the same bounds could scoop up all three same-day orders at once, because toRANGE, they're all "the same point in time."
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- always exactly 3 rows
)
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.