SQL / WINDOW FUNCTIONS
LAG and LEAD for peeking at neighbouring rows
Compare each row with its neighbours using LAG and LEAD to compute deltas, gaps, and change flags without writing a self join.
What you will learn
- Compute a row-to-row delta with meter - LAG(meter) OVER (ORDER BY day_no)
- Replace the NULL at a partition edge using the third argument of LAG or LEAD
- Use PARTITION BY so a delta never crosses an account or product boundary
- Recognise that the offset counts rows, not days, when dates are missing
Understanding LAG and LEAD for peeking at neighbouring rows
LAG and LEAD do not summarise anything. They take the rows that ORDER BY has lined up inside the window and read a value out of a different row: LAG looks backwards, LEAD forwards, and what comes back is a genuine value from a real row. The mental model is a queue of rows with a finger on the current one; LAG(x, 2) walks the finger two places back, reads x, and returns. When the finger walks off the front there is nothing to read, which is why the first row of an ordered window always has a NULL LAG and the last row always has a NULL LEAD.
Both take up to three arguments: the expression, an offset that defaults to 1, and a value to use instead of NULL when the offset lands outside the partition. The offset is counted in rows, never in the units of the ordering column, and that is the expensive thing to get wrong. LAG(meter) OVER (ORDER BY reading_day) gives the previous row that exists in the data, so if a day was never recorded you silently get the reading from two days back and the difference covers a longer span than you think. If you genuinely mean the value 24 hours earlier, that is a join against a calendar or a RANGE-based frame, not LAG.
PARTITION BY matters more here than for most window functions, because the offset itself is what gets reset: inside each partition the first row's LAG is NULL again, so a per-account delta can never subtract the last row of some other account. Two further details decide whether the answer is reproducible. If the ORDER BY inside OVER has ties, which tied row counts as previous is arbitrary and can change between runs, so add a unique tiebreaker. And because windows are evaluated after WHERE, removing rows changes who the neighbours are, so a filter you thought was harmless can quietly rewrite every delta, and filtering on the delta itself has to happen in a wrapping query.
WITH reading (day_no, meter) AS (
VALUES (1, 1200), (2, 1215), (3, 1215), (4, 1260), (5, 1290)
)
SELECT
day_no,
meter,
LAG(meter) OVER (ORDER BY day_no) AS prev_meter,
meter - LAG(meter) OVER (ORDER BY day_no) AS used_today,
LEAD(meter, 1, meter) OVER (ORDER BY day_no) AS next_or_same
FROM reading
ORDER BY day_no;LAG and LEAD locate a row by counting a fixed number of rows away in the window's ORDER BY, so 'previous' means the previous row present, not the previous value of the ordering column.
Worked examples
Direction of change inside each group
Shows how PARTITION BY restarts the backwards lookup so one product's first row cannot read another product's last row.
WITH price (product, day_no, cents) AS (
VALUES ('mug', 1, 800), ('mug', 2, 850), ('mug', 3, 850),
('pen', 1, 120), ('pen', 2, 100)
)
SELECT
product,
day_no,
cents,
LAG(cents) OVER (PARTITION BY product ORDER BY day_no) AS prev_cents,
CASE
WHEN LAG(cents) OVER (PARTITION BY product ORDER BY day_no) IS NULL THEN 'first'
WHEN cents > LAG(cents) OVER (PARTITION BY product ORDER BY day_no) THEN 'up'
WHEN cents < LAG(cents) OVER (PARTITION BY product ORDER BY day_no) THEN 'down'
ELSE 'flat'
END AS move
FROM price
ORDER BY product, day_no;Example explained
Line 1PARTITION BY product restarts the offset, so the 'pen' row for day 1 gets NULL rather than picking up the last 'mug' price.
Line 2The ORDER BY day_no inside OVER decides what previous means; the ORDER BY at the end of the query only controls print order.
Line 3The identical OVER clause is repeated in every branch because a select-list alias such as prev_cents cannot be referenced from the same SELECT.
Line 4The IS NULL branch has to be tested first: for a partition's first row both comparisons are NULL, so the CASE would otherwise fall through and label it 'flat'.
Offsets larger than one, and the default argument
Measures the gap forward to the next event and reaches two rows back, showing that the offset is a row count and the third argument fills the edges.
WITH event (id, at_sec) AS (
VALUES (1, 0), (2, 30), (3, 45), (4, 900), (5, 930)
)
SELECT
id,
at_sec,
LEAD(at_sec) OVER (ORDER BY at_sec) - at_sec AS gap_to_next,
LAG(at_sec, 2, -1) OVER (ORDER BY at_sec) AS two_back
FROM event
ORDER BY at_sec;Example explained
Line 1LEAD(at_sec) - at_sec measures forward to the following row; row 5 has no follower, so gap_to_next is NULL and not 0.
Line 2gap_to_next of 855 on row 3 proves the offset is one row, not one second: nothing exists between 45 and 900.
Line 3LAG(at_sec, 2, -1) skips two rows back, so rows 1 and 2 fall off the start of the window and take the -1 default instead of NULL.
Line 4Both calls share ORDER BY at_sec, so 'next' and 'two back' are counted along the same sequence of rows.
Important notes
LAG and LEAD ignore the window frame and always look across the whole partition, so adding ROWS BETWEEN 1 PRECEDING AND CURRENT ROW changes nothing for them even though it changes SUM.
Keep the offset a non-negative integer constant for portability; LAG(x, 0) simply returns the current row's own value, and these functions need SQLite 3.25+, MySQL 8.0+, or MariaDB 10.2+.
Common mistakes
Reading LAG(x) OVER (ORDER BY d) as 'x on the previous date'. If a date is missing from the table it returns the last recorded row instead, so a multi-day change gets reported as a single day's change.
Writing the comparison in WHERE, as in WHERE meter - LAG(meter) OVER (ORDER BY day_no) > 20. The database rejects it because windows are computed after WHERE; the test has to move into an outer query or CTE.
Forgetting that the first row's difference is NULL and then filtering on that difference. The first row of every partition vanishes without an error and any total computed from the deltas comes out short.
Try it yourself
Change, predict, then run
Copy the reading table from the main example, add a sixth row (6, 1290), then return only the days where the meter rose by more than 20 since the previous reading. You will need a CTE or subquery, because the comparison cannot sit in WHERE.
Open the SQL workspaceCheck your understanding
A deliveries table holds at most one row per date, and some dates have no row at all. For a delivery whose closest earlier delivery was three days before, what does delivered_on - LAG(delivered_on) OVER (ORDER BY delivered_on) return?
- 1, because ORDER BY delivered_on makes LAG step back one day in that column
- NULL, because no row exists exactly one day earlier
- 3, because LAG returns the previous row in the sorted window whatever its date
- 0, because LAG falls back to the current row when the preceding day is missing
Show answer
The offset in LAG is a number of rows in the window's ordering, not a distance in the ordering column's units, so the previous row is just the closest earlier delivery and the subtraction yields 3. The first option is the usual trap: ORDER BY delivered_on only fixes the sequence the rows are lined up in, it does not redefine LAG as 'one day earlier'. NULL appears only when there is no earlier row at all in the partition.