Comparing with the previous row using LAG and LEAD

Look one row back or ahead, to measure change between periods or time spent between events.

How much did revenue change from last month? How long did this deal sit in each stage? Both questions compare a row with the one before it. LAG reaches back a row, and LEAD reaches forward.

This lesson uses the new opportunity_history table. It’s a log with one row for every time a deal changed stage: history_id, opportunity_id, stage and changed_at.

The shape

SELECT opportunity_id,
       stage,
       changed_at,
       LAG(stage)  OVER (PARTITION BY opportunity_id ORDER BY changed_at) AS previous_stage,
       LEAD(stage) OVER (PARTITION BY opportunity_id ORDER BY changed_at) AS next_stage
FROM opportunity_history
WHERE opportunity_id = 1001;

The first row has no previous row, so LAG returns NULL. The last row has no next row, so LEAD does too.

Time between events

Subtract the previous timestamp from the current one, and you get how long the deal spent in the previous stage (lesson 21’s julianday):

WITH changes AS (
    SELECT LAG(stage) OVER (PARTITION BY opportunity_id ORDER BY changed_at) AS from_stage,
           julianday(changed_at)
             - julianday(LAG(changed_at) OVER (PARTITION BY opportunity_id
                                               ORDER BY changed_at)) AS days_in_stage
    FROM opportunity_history
)
SELECT from_stage,
       COUNT(*)                     AS moves,
       ROUND(AVG(days_in_stage), 1) AS avg_days
FROM changes
WHERE from_stage IS NOT NULL
GROUP BY from_stage
ORDER BY avg_days DESC;

Deals sit longest in Prospecting. That’s the kind of finding a sales manager acts on. Lesson 33 shows why the median might tell a slightly different story.

Change from the previous period

Group by month first, then compare each month with the one before:

WITH monthly AS (
    SELECT strftime('%Y-%m', closed_at) AS month,
           SUM(amount) AS won
    FROM opportunities
    WHERE is_won = 1
      AND closed_at >= '2026-01-01'
    GROUP BY month
)
SELECT month,
       won,
       LAG(won) OVER (ORDER BY month)       AS prev_month,
       won - LAG(won) OVER (ORDER BY month) AS change
FROM monthly;

Look closely at June. Its “previous month” is April, because no deals were won in May, so May has no row. LAG means the previous row, not the previous calendar month. Lesson 34 shows how to fill in the missing months so LAG lines up with the calendar.

Percent change is lesson 18’s formula with LAG as the old value: 100.0 * (won - LAG(won) OVER (...)) / NULLIF(LAG(won) OVER (...), 0).

More than one row back, and defaults

LAG(won, 12) OVER (ORDER BY month)      -- 12 rows back: same month last year, if no months are missing
LAG(won, 1, 0) OVER (ORDER BY month)    -- 0 instead of NULL on the first row

Filtering on LAG

Like every window function (lesson 29), LAG can’t go in WHERE. Calculate it in a CTE, then filter outside.

Common mistakes

Dialect notes

LAG and LEAD work the same in SQL Server, PostgreSQL, MySQL 8.0+ and SQLite, including the optional offset and default. The date math differs: SQL Server uses DATEDIFF(day, LAG(changed_at) OVER (...), changed_at).

Snowflake notes

Same syntax. Snowflake also has LAG(...) IGNORE NULLS, which skips rows where the value is NULL and returns the last real one. That’s useful for “carry the last known value forward” on gappy data. Pair LAG with DATEDIFF('day', ...) for days between events, and remember DATEDIFF counts boundaries crossed (lesson 21).

More in the field guide: LAG / LEAD and Elapsed time. Exercises A2 and M5 are the two patterns above.

Try it

Turn the history log into a list of moves: every stage change, showing the deal, the stage it came from and the stage it moved to. Skip each deal’s first row, which has no previous stage. Return opportunity_id, from_stage and to_stage.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson