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

How to think about it

The interviewer is really asking: “Can you compute period-over-period comparisons without writing a self-join?” LAG and LEAD are the clean, readable answer.

Think of them as a sliding window that peeks at a neighboring row: LAG looks back, LEAD looks forward. Both work within an OVER (PARTITION BY ... ORDER BY ...) clause so you can scope the lookback to a customer, region, or product independently.

LAG(expr, offset, default) and LEAD(expr, offset, default) let you reach into adjacent rows without a self-join.

Syntax:

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) to look; defaults to 1.
• default — value returned when the target row doesn’t exist (first/last row in partition); defaults to NULL.

Month-over-month revenue change — run it

The playground below lets you see LAG in action on a small monthly revenue table. Notice the first row gets NULL for mom_change — that’s the edge case to handle.

How the pieces fit together

• PARTITION BY region — resets the window for each region independently.
• ORDER BY month — determines which row is “previous.”
• The third argument (default) substitutes a value when no prior row exists: LAG(revenue, 1, 0) returns 0 instead of NULL for the first row.

LEAD use-case — compute time between sessions:

SELECT
  user_id, session_start,
  LEAD(session_start) OVER (PARTITION BY user_id ORDER BY session_start) AS next_session_start
FROM sessions;

Multi-period lookback (year-over-year):

LAG(revenue, 12) OVER (PARTITION BY region ORDER BY month)
-- revenue from exactly 12 months ago