LAG, LEAD and FIRST_VALUE: match-to-match change

Part of the free LogicWiz SQL & Databases course, Module 11: Window functions. The queries and practice run in the app on real Postgres.

Kohli scored 81, then 102, then 9, then 59. The commentator asks: how much better or worse was each innings than the one before? Every number you need is in the row above, and plain SQL cannot see the row above.

You'll learn to

Read the previous or next row with LAG and LEAD
Compute change from the previous row, with NULL or a default for the first row
Compare every row with the first (or best) row of its group using FIRST_VALUE
Keep each player's rows separate with PARTITION BY

Learn

Looking one row back

LAG(runs) returns runs from the previous row of the window. LEAD(runs) returns it from the next row.

"Previous" means previous in the window's ORDER BY, so the ORDER BY inside OVER is required for a meaningful answer. The first row has no previous row, so its LAG is NULL; the last row's LEAD is NULL.

Kohli's innings, match to match

SELECT m.match_date, b.runs,
       LAG(b.runs) OVER (ORDER BY m.match_date) AS prev_runs,
       b.runs - LAG(b.runs) OVER (ORDER BY m.match_date) AS change
FROM batting b
JOIN matches m ON m.match_id = b.match_id
WHERE b.player_id = 7
ORDER BY m.match_date;

81 has no previous innings, so prev_runs and change are NULL. Then +21, then -93, then +50.

Offset and default

LAG and LEAD take up to three arguments: LAG(value, offset, default).

offset is how many rows back (default 1). LAG(runs, 2) looks two innings back.
default replaces the NULL when there is no such row. LAG(runs, 1, 0) gives 0 for the first innings.

Pick the default carefully: 0 runs before a debut makes the "change" look like a huge jump. Often NULL is the honest answer.

Every player at once: PARTITION BY

SELECT p.name, m.match_date, b.runs,
       LAG(b.runs) OVER w AS prev_runs,
       LEAD(m.match_date) OVER w AS next_match
FROM batting b
JOIN players p ON p.player_id = b.player_id
JOIN matches m ON m.match_id = b.match_id
WHERE p.team_id = 1
WINDOW w AS (PARTITION BY b.player_id ORDER BY m.match_date)
ORDER BY p.name, m.match_date;

PARTITION BY player_id gives each CSK player his own list, so Dhoni's first innings does not pick up Gaikwad's runs. WINDOW w AS (...) names a window once so you can reuse it.

Forget PARTITION BY player_id and LAG walks straight across players: one player's first innings gets the previous player's last score. The numbers look plausible, which is why this bug survives code review.

FIRST_VALUE: compare with the top of the window

FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY y) gives x from the first row of each window. Order by runs descending and the first row is the top score of the match, so FIRST_VALUE(runs) - runs is "how far behind the top scorer".

LAST_VALUE looks like its twin, but it has a trap with the default frame. Lesson 4 explains it; until then, use FIRST_VALUE with the order flipped.

How far behind the top scorer?

SELECT b.match_id, p.name, b.runs,
       FIRST_VALUE(p.name) OVER (PARTITION BY b.match_id ORDER BY b.runs DESC) AS top_scorer,
       FIRST_VALUE(b.runs) OVER (PARTITION BY b.match_id ORDER BY b.runs DESC) - b.runs AS behind
FROM batting b
JOIN players p ON p.player_id = b.player_id
WHERE b.match_id IN (1, 4)
ORDER BY b.match_id, b.runs DESC;

LAG is behind many interview favourites: month-over-month growth (revenue - LAG(revenue)), days since the last order (order_date - LAG(order_date)), and consecutive login streaks. Before LAG existed, people used a self join on id = id - 1, which breaks when ids have gaps.

In one line

LAG(x) / LEAD(x) read the previous / next row in the window order (NULL at the edges); always PARTITION BY the entity you are following.

Interview corner

How do you calculate month-over-month growth in SQL?

Aggregate to one row per month, then compare with LAG: SELECT month, revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct FROM monthly;. The first month is NULL; guard a zero previous month with NULLIF.

LAG vs a self join: why prefer LAG?

A self join on id = prev.id + 1 assumes ids have no gaps and costs another scan and join. LAG follows the real order (dates, scores) inside each partition, skips gaps naturally and reads the table once.

What do LAG and LEAD return at the edges of a partition?

NULL, unless you pass a default as the third argument: LAG(runs, 1, 0). Say why you chose NULL or a default, since a fake 0 can look like real data.