LAG and LEAD

LAG and LEAD read a value from a nearby row in the same partition, turning row-to-row comparison into a single pass.

SyntaxLAG(expr [, N [, default]]) OVER ([PARTITION BY ...] ORDER BY expr ...) -- LEAD is identical, looking forward

LAG reads a value from a row before the current one in the window ordering; LEAD reads from a row after it. Both replace the self-join that would otherwise be needed to compare a row with its neighbour, and both are evaluated in one pass.

The arguments

The signatures are LAG(expr [, N [, default]]) and LEAD(expr [, N [, default]]). If N or default are missing the defaults are 1 and NULL, so LAG(price) is shorthand for LAG(price, 1, NULL) — the same function call written out. N must be a nonnegative integer literal (or a ? parameter, user variable or stored routine variable) in the range 0 to 2^63, and cannot be NULL. N = 0 evaluates expr for the current row.

Edges and NULLs

At a partition boundary there is no neighbouring row, so the function returns default — NULL unless you supplied something else. That NULL then propagates through arithmetic, which is why value - LAG(value) OVER w is NULL on the first row of every partition. Either supply a third argument or handle the NULL explicitly. MySQL 8.4 parses the null_treatment clause but implements only RESPECT NULLS; IGNORE NULLS raises an error, so there is no built-in "last non-null value" form.

Example

Example · sql
-- Day-over-day change in signups.
SELECT
  d.metric_day,
  d.signups,
  -- LAG(d.signups) is shorthand for LAG(d.signups, 1, NULL).
  LAG(d.signups)                    OVER w AS prev_day,
  LEAD(d.signups)                   OVER w AS next_day,
  -- Third argument 0 instead of NULL, so the first day's delta is a number.
  d.signups - LAG(d.signups, 1, 0)  OVER w AS delta,
  ROUND(100 * (d.signups - LAG(d.signups) OVER w)
            / NULLIF(LAG(d.signups) OVER w, 0), 1) AS pct_change  -- guards /0
FROM daily_signups AS d
WHERE d.metric_day >= '2026-01-01'
WINDOW w AS (ORDER BY d.metric_day)
ORDER BY d.metric_day;

When to use it

  • Compute day-over-day change in signups or revenue.
  • Flag the rows where a status actually changed.
  • Measure the elapsed gap between consecutive events in a session.
  • Compare a reading to the one N rows earlier without a self-join.

More examples

Change detection: only real transitions

Event tables often record state on every heartbeat, not only on change. This collapses them to the transitions, and the IS NULL branch is required because <> against NULL is never true.

Example · sql
-- Emit one row per genuine status change, dropping repeated identical events.
WITH events AS (
  SELECT subscription_id, id, changed_at, status,
         LAG(status) OVER (PARTITION BY subscription_id
                           ORDER BY changed_at, id) AS prev_status
  FROM subscription_events
)
SELECT subscription_id, changed_at, prev_status, status
FROM events
WHERE prev_status IS NULL     -- the first event for this subscription
   OR prev_status <> status   -- an actual transition
ORDER BY subscription_id, changed_at;

Gaps between consecutive events

Sessionisation and idle-timeout detection. Note the filter has to live outside the window level, and that the first row's NULL is quietly excluded by the comparison, which is usually what you want here.

Example · sql
-- Page views more than 30 minutes after the previous one in the same session.
WITH gaps AS (
  SELECT session_id, viewed_at,
         -- TIMESTAMPDIFF(unit, a, b) returns b - a.
         TIMESTAMPDIFF(MINUTE,
           LAG(viewed_at) OVER (PARTITION BY session_id ORDER BY viewed_at, id),
           viewed_at) AS minutes_since_prev
  FROM page_views
)
SELECT session_id, viewed_at, minutes_since_prev
FROM gaps
WHERE minutes_since_prev > 30   -- NULL on the first view, so it is excluded
ORDER BY session_id, viewed_at;

Offsets and defaults spelled out

Shows that the offset is in rows, not in time: LAG(price, 7) is seven recorded quotes back, which is only seven days back if the table has exactly one row per day. For a time-based look-back use a RANGE frame instead.

Example · sql
-- One window, read at four different offsets.
SELECT
  q.symbol, q.ts, q.price,
  LAG(q.price, 0)        OVER w AS same_row,     -- N = 0 -> the current row
  LAG(q.price, 1)        OVER w AS prev_tick,    -- same as LAG(q.price)
  LAG(q.price, 7, 0)     OVER w AS seven_back,   -- 0 instead of NULL at the edge
  LEAD(q.price, 7)       OVER w AS seven_ahead
FROM quotes AS q
WINDOW w AS (PARTITION BY q.symbol ORDER BY q.ts, q.id);

Related

sql window introsql partition bysql framessql date functionssql running totals

Discussion

  • Be the first to comment on this lesson.