How do LAG and LEAD work, and how would you use them to compute month-over-month revenue change?

How to think about it

What the interviewer is really asking: “Can you compute period-over-period comparisons without a self-join?” LAG and LEAD are the clean, readable answer. Picture a sliding window that peeks at a neighbouring row — LAG looks back, LEAD looks forward — both scoped by an OVER (PARTITION BY ... ORDER BY ...) clause so the lookback resets per customer, region, or product:

LAG  (column [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)
LEAD (column [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)

• offset — how many rows back (LAG) or forward (LEAD); defaults to 1.
• default — the value returned when the target row doesn’t exist (first/last in the partition); defaults to NULL.

A worked example — month-over-month change

PARTITION BY region keeps each region’s window separate; ORDER BY month defines “previous”; subtracting the lagged revenue gives the change:

SELECT region, month, revenue,
       LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue,
       revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS mom_change
FROM monthly_revenue
ORDER BY region, month;

region	month	revenue	prev_revenue	mom_change
East	2024-01	3000	NULL	NULL
East	2024-02	3200	3000	200
East	2024-03	3800	3200	600
East	2024-04	2900	3800	-900
West	2024-01	4000	NULL	NULL
West	2024-02	4800	4000	800
West	2024-03	3900	4800	-900
West	2024-04	5200	3900	1300

Each region’s January row has no predecessor inside its own partition, so prev_revenue is NULL and the arithmetic yields NULL — exactly once per region, not once for the whole table. Everywhere else, revenue − prev_revenue reads off the month-over-month delta: East dips 900 in April, West jumps 1300.

How the pieces generalise

• The third argument replaces the missing value: LAG(revenue, 1, 0) returns 0 instead of NULL for the first row.
• LEAD powers “time until next event” — LEAD(session_start) OVER (PARTITION BY user_id ORDER BY session_start) gives each session’s successor.
• A larger offset reaches further back: LAG(revenue, 12) compares against the same month a year ago.