LAG, LEAD, FIRST_VALUE & LAST_VALUE

"Did Kohli score more than in his previous match?" To answer that you need two rows at once — this match and the last one. Before window functions this needed a self-join. Value functions simply reach into another row of the window and bring its value to the current row.

Definition

A value (offset) window function returns a value taken from a different row of the window, located relative to the current row (LAG / LEAD) or at the window's edges (FIRST_VALUE / LAST_VALUE). ORDER BY inside OVER is required, because "previous" and "first" only make sense in a defined order.

• offset — how many rows to move; optional, default 1.
• default — the value returned when that row does not exist (before the first / after the last row of the partition); optional, default NULL.
• Rows are never removed; partitions are respected — LAG never looks into another player's rows.

Syntax

LAG(expression [, offset [, default]])  OVER ([PARTITION BY ...] ORDER BY ...)
LEAD(expression [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...)
FIRST_VALUE(expression) OVER ([PARTITION BY ...] ORDER BY ... [frame])
LAST_VALUE(expression)  OVER ([PARTITION BY ...] ORDER BY ... [frame])

LAG/LEAD do not allow a frame; FIRST_VALUE/LAST_VALUE do (and LAST_VALUE usually needs one — see below).

Example — LAG and LEAD: previous and next match

SELECT id, player, match_date, runs,
    LAG(runs)  OVER (PARTITION BY player ORDER BY match_date) AS prev_runs,
    LEAD(runs) OVER (PARTITION BY player ORDER BY match_date) AS next_runs
FROM innings;

Step by step: (1) partition by player, order by date; (2) LAG fetches the row before, LEAD the row after. The first match of each player has no previous row → NULL; the last match has no next row → NULL. Dhoni's rows 7 and 9 also show NULL — not because the row is missing, but because the neighbouring row's value (2025-03-31) is NULL.

Example — LAG with offset and default

SELECT id, player, runs,
    LAG(runs, 2, 0) OVER (PARTITION BY player ORDER BY match_date) AS two_back
FROM innings;

Offset 2 = two rows back; default 0 replaces the NULL you would get when there is no row two back (the first two matches of each player).

Careful: the default is used only when the target row does not exist. If the row exists but its value is NULL, you still get NULL. E.g. LAG(runs, 1, 0) on Dhoni's 2025-04-07 innings returns NULL (the previous row exists; its runs are NULL). Use COALESCE(LAG(runs) OVER (...), 0) to replace both cases.

Example — match-over-match change

SELECT id, player, runs,
    runs - LAG(runs) OVER (PARTITION BY player ORDER BY match_date) AS change
FROM innings;

Step by step: (1) LAG gives last match's runs; (2) subtract it from this row. Positive = better form, negative = worse. This is time-series analysis — the same pattern gives month-over-month sales growth, day-over-day price change, etc.

Example — FIRST_VALUE and LAST_VALUE

SELECT id, player, runs,
    FIRST_VALUE(runs) OVER (PARTITION BY player ORDER BY match_date) AS first_runs,
    LAST_VALUE(runs)  OVER (PARTITION BY player ORDER BY match_date) AS last_wrong,
    LAST_VALUE(runs)  OVER (
        PARTITION BY player
        ORDER BY match_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_runs
FROM innings;

Why is last_wrong wrong? With ORDER BY and no frame, the default frame is "first row → current row". So the "last row of the frame" is the current row itself, and LAST_VALUE just returns the current runs. LAST_VALUE needs the full frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to see the true last match. FIRST_VALUE works with the default frame because the frame always starts at the first row.

Tip: an easy alternative for the last value is FIRST_VALUE(runs) OVER (PARTITION BY player ORDER BY match_date DESC) — reverse the order and take the first.

Common mistakes

• ❌ LAST_VALUE with the default frame → returns the current row ✅ add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• ❌ Forgetting PARTITION BY → LAG would take Rohit's previous value from Kohli's last match ✅ partition by the entity (player, customer…).
• ❌ Expecting the LAG default to replace a NULL value → ✅ it only covers a missing row; use COALESCE for NULL values.
• ❌ LAG without ORDER BY → ✅ ORDER BY is required.

SQL Server vs MySQL / PostgreSQL

All support LAG/LEAD/FIRST_VALUE/LAST_VALUE with the same syntax (SQL Server 2012+, MySQL 8+, PostgreSQL). SQL Server 2022 also added IGNORE NULLS for FIRST_VALUE/LAST_VALUE to skip NULL values.

Interview tip

Q: How do you calculate the change from the previous row without a self-join? A: value - LAG(value) OVER (PARTITION BY entity ORDER BY date). LAG brings the previous row's value onto the current row.