LAG and LEAD: Period-over-Period

Compare a row to its neighbor without a self-join

"Month-over-month revenue change" and "days since a customer's previous order" are two of the most-requested analytics metrics, and the naive approach is a self-join: join the table to itself on month = month - 1. That works, but it is verbose, scans the table twice, and silently breaks when a period is missing. LAG and LEAD express the same idea in one line and one pass.

The mental model

Think of a window as one ordered filmstrip per group. PARTITION BY customer_id splits rows into a separate strip per customer, and ORDER BY order_month decides which frame comes "before" and "after." LAG(col, n) reads col from the frame n positions back (default n = 1); LEAD(col, n) reads n positions forward. Nothing is joined. Each row just peeks at a neighbor inside its own ordered strip.

Table

Customer 1's ordered strip. LAG reads one row back, LEAD one row forward. Note March is missing: April's LAG is February (150), because LAG is positional in the result, never calendar-aware.

order_month	revenue	prev (LAG)	next (LEAD)
2024-01	100	NULL	150
2024-02	150	100	90
2024-04	90	150	NULL

SELECT
  customer_id,
  order_month,
  revenue,
  LAG(revenue) OVER (PARTITION BY customer_id ORDER BY order_month) AS prev_revenue,
  revenue - LAG(revenue) OVER (PARTITION BY customer_id ORDER BY order_month) AS mom_delta
FROM monthly_revenue;

-- customer_id | order_month | revenue | prev_revenue | mom_delta
--      1       |  2024-01    |   100   |    NULL      |   NULL
--      1       |  2024-02    |   150   |    100       |    50
--      1       |  2024-04    |    90   |    150       |   -60

A customer's first month has no earlier frame, so LAG returns NULL and the delta is NULL. That is a real "no prior period" signal, not a bug. To substitute a value, pass a third argument: LAG(revenue, 1, 0) yields 0 instead of NULL.

Notice the last row. March is missing, so April's LAG returns February (150), not "one calendar month back." LAG is positional in the result, never calendar-aware.

Pitfalls

• No ORDER BY means a meaningless offset. Without it the window has no defined order, so "previous" is arbitrary. Always order by your time key.
• Gaps are positional. If a month is missing, LAG still returns the previous row, not the previous calendar month. When you need strict calendar adjacency, left-join onto a dense date spine (dim_date) first so every month exists.
• Percent-change division. pct_change = (revenue - prev_revenue) * 100.0 / prev_revenue breaks two ways: it is NULL when prev_revenue is NULL, and dividing by 0 raises an error in Postgres and most warehouses (SQLite quietly returns NULL). Guard with NULLIF(prev_revenue, 0), and keep the 100.0 so integer division does not truncate the result to a whole number.

To flag a customer's most recent month, check whether the row has no successor. LEAD(order_month) OVER (PARTITION BY customer_id ORDER BY order_month) IS NULL is true only on the last frame of each strip.

Interview nuance: window functions are computed after WHERE, GROUP BY, and HAVING have run, as part of evaluating the SELECT list. You therefore cannot filter on LAG/LEAD (or reference their aliases) in a WHERE or HAVING clause at the same query level. Wrap the window query in a CTE or subquery, then filter or flag on its output. That evaluation order is the single most common thing interviewers probe about window functions.

In the warehouse this differs. Snowflake lets LAG / LEAD / FIRST_VALUE skip nulls with IGNORE NULLS, the usual "carry the last non-null value forward" trick, and Oracle and BigQuery accept IGNORE NULLS on FIRST_VALUE / LAST_VALUE. BigQuery's LAG / LEAD do not take it, though, so don't assume the clause is portable across every function. SQLite and Postgres have no IGNORE NULLS at all; writing it is a bare syntax error near "NULLS". Carry the value forward with a correlated subquery instead:

-- Warehouses: LAG(status) IGNORE NULLS OVER (PARTITION BY id ORDER BY ts)
-- Portable: the most recent non-null status at or before this row.
SELECT id, ts, status,
  (SELECT s2.status FROM events s2
   WHERE s2.id = e.id AND s2.ts <= e.ts AND s2.status IS NOT NULL
   ORDER BY s2.ts DESC LIMIT 1) AS status_filled
FROM events e;

This lesson runs a multi-statement script against a fresh in-memory SQLite database, then hidden queries check your offsets, deltas, and row count. Lead your load with DELETE FROM <target>; so a re-run does not stack duplicate rows.