~/learn/SQL · Window functions
Frames, LAG and LEAD
Moving averages and row-to-row comparisons.
why this matters
Moving averages smooth out noisy daily numbers, and comparing a row to the one before it is how you spot spikes, drops and churn.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
LAG and LEAD read the previous and next row inside a partition — the basis of every change, delta and streak calculation.
Try it: Swap LAG for LEAD to compare against the next day.
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.
Note
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.
What a frame is
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW day 1 ─┐ day 2 ─┼─ average of these 3 day 3 ─┘ ← current row
Common gotcha
LAG on the first row returns NULL because there is no previous row. Use LAG(x, 1, 0) to default it to zero.
Pro tip
Always add ORDER BY inside OVER(...) for LAG, LEAD and running totals — without it the order is undefined.
key takeaways
- A frame picks which rows around the current one get aggregated.
- LAG looks backwards, LEAD looks forwards.
- ORDER BY inside OVER decides what 'previous' means.
check your understanding
Which gives a 3-day moving average ending on the current day?
free practice
Build confidence with a question you can solve without Pro.