LAG / LEAD
Read a value from a previous or following row without a self-join — the basis of period-over-period comparisons.
Syntax
LAG(expression [, offset [, default]]) OVER (
[PARTITION BY partition_expression, ...]
ORDER BY sort_expression [ASC | DESC], ...
)
LEAD(expression [, offset [, default]]) OVER ( ... )| Parameter | Type | Notes |
|---|---|---|
| expression* | any | The value to read from the neighbouring row. |
| offset | integer | How many rows back ( |
| default | same type as expression | Returned when there's no such row (start/end of partition). Defaults to |
What it does
LAG() returns expression from offset rows before the current row in
the window's order; LEAD() looks offset rows after.
SQL playground
Loading editor…
⌘/Ctrl + Enter
January has no prior row, so prev_month_revenue and mom_change are NULL
there — usually the right behavior, since there's nothing to compare against.
Per partition
SQL playground
Loading editor…
⌘/Ctrl + Enter
PARTITION BY customer_id means LAG never looks across a customer boundary
— the first order for each customer correctly has no "previous" order.
Supplying a default instead of NULL
SQL playground
Loading editor…
⌘/Ctrl + Enter
Gotchas
- The window
ORDER BYdefines "previous" — without a deterministic order (add a tiebreaker), the rowLAGpicks on a tie is arbitrary. - Boundary rows need a decision.
NULL,0, or a supplieddefaultall mean different things downstream — pick deliberately, don't let it default silently. PARTITION BYresets the lookback. Omit it only when you actually want to read across group boundaries.
- Running total — the other common "compare across rows" window function.
- Gaps and islands — LAG used to detect jumps in a sequence.
- Running totals & moving averages — LAG for month-over-month percent change.
Safety check before shipping
- decide what the boundary rows should show — NULL, 0, or a supplied default
- confirm the window ORDER BY is deterministic
- check whether the comparison should reset per partition
Was this page helpful?