Frames, LAG and LEAD

LAG and LEAD read the previous and next row inside a partition — the basis of every change, delta and streak calculation.

A frame clause says which rows around the current row take part. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three-row moving average; the default frame with ORDER BY is a running total.

ROWS counts physical rows; RANGE counts rows with equal ORDER BY values as one unit. With ties they give different answers — say which one you mean.

FIRST_VALUE and LAST_VALUE pull a reference row into every row of the partition, for example each product's price against the priciest in its category.