SQL · Guides · October 30, 2026 · 13 min read

SQL LAG and LEAD functions: previous and next row, with examples

LAG(column) returns the value of column from the previous row, and LEAD(column) from the next row, in the order you set with OVER (ORDER BY ...). Both keep every row in the result, so you can put “this period” and “last period” side by side and subtract. Add PARTITION BY to look only at earlier or later rows of the same customer, product or flight. The full syntax is LAG(column, offset, default): offset is how many rows back (1 by default), default is what to return when there is no such row (NULL by default).

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output. LAG and LEAD are window functions; if OVER and PARTITION BY are new to you, start with the SQL window functions guide.

LAG and LEAD: the previous and the next row

Daily bookings in early August, with the day before and the day after on each row:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-06'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       LAG(bookings)  OVER (ORDER BY day) AS prev_day,
       LEAD(bookings) OVER (ORDER BY day) AS next_day
FROM daily
ORDER BY day;

The first row has no previous row, so LAG returns NULL; the last row has no next one, so LEAD returns NULL. Note that the window only sees rows that passed WHERE: 1 August has no “previous day” here because 31 July was filtered out, not because there were no bookings.

LEAD(x) is the same as LAG(x) with the order reversed: LEAD(bookings) OVER (ORDER BY day) equals LAG(bookings) OVER (ORDER BY day DESC). Use whichever reads more naturally.

Offset and default value

The second argument looks further back, the third replaces the NULL when there is nothing to look at:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-07-01' AND book_date < '2026-08-01'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       LAG(bookings, 1, 0) OVER (ORDER BY day) AS prev_or_zero,
       LAG(bookings, 7)    OVER (ORDER BY day) AS same_day_last_week,
       ROUND(100.0 * (bookings - LAG(bookings, 7) OVER (ORDER BY day))
             / LAG(bookings, 7) OVER (ORDER BY day), 1) AS wow_pct
FROM daily
ORDER BY day
LIMIT 10;

(First 10 days of July shown.)

• LAG(bookings, 7) compares Wednesday with last Wednesday. For daily data with a weekly rhythm, this is a fairer comparison than with yesterday.
• LAG(bookings, 1, 0) returns 0 on the first row. Be careful with defaults: a 0 here means “no previous row”, not “no bookings yesterday”, and a percentage change against 0 is a division by zero. For change calculations, the NULL is usually the honest answer.
• The default must have a type compatible with the column. LAG(day, 1, 'none') on a date column fails.

LAG(x, 7) means seven rows back, not seven days. If a day is missing from daily, the comparison silently shifts. Build a full calendar first if gaps are possible; see the date spine guide.

Change vs the previous period

The most common use of LAG: week-over-week revenue, absolute and in percent. Full weeks only, from 29 June to 9 August:

WITH weekly AS (
  SELECT CAST(date_trunc('week', book_date) AS date) AS week,
         SUM(total_amount) AS revenue
  FROM bookings
  WHERE book_date >= '2026-06-29' AND book_date < '2026-08-10'
  GROUP BY 1
)
SELECT week, revenue,
       LAG(revenue) OVER (ORDER BY week) AS prev_week,
       revenue - LAG(revenue) OVER (ORDER BY week) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week))
             / LAG(revenue) OVER (ORDER BY week), 1) AS pct_change
FROM weekly
ORDER BY week;

Revenue is in US dollars. The window runs after GROUP BY, so it works on the weekly totals, not on individual bookings. Two habits help here:

• Drop incomplete periods. The data starts mid-week on 17 June and ends on 11 August; including those partial weeks would show a fake jump at the start and a fake collapse at the end.
• Write 100.0, not 100. In PostgreSQL and SQL Server, integer divided by integer is an integer, and small changes round to 0. Here revenue is a decimal, so it would survive, but on a COUNT(*) it would not.

Repeating LAG(revenue) OVER (ORDER BY week) three times is verbose but harmless: the identical windows share one sort. For readability, you can also compute prev_week in a CTE and do the arithmetic in the outer query.

LAG with PARTITION BY

Without PARTITION BY, the window is the whole result. That is fine for one time series, but wrong as soon as the rows belong to several entities. Here are the flights of two loyalty members, with the previous arrival airport computed both ways:

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tm.member_id IN (2969, 12589)
), x AS (
  SELECT member_id, scheduled_departure,
         LAG(arrival_airport) OVER (ORDER BY member_id, scheduled_departure) AS prev_no_partition,
         LAG(arrival_airport) OVER (PARTITION BY member_id ORDER BY scheduled_departure) AS prev_partitioned,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY scheduled_departure) AS n
  FROM trips
)
SELECT member_id, scheduled_departure, prev_no_partition, prev_partitioned
FROM x
WHERE n <= 2
ORDER BY member_id, scheduled_departure;

Without partitioning, member 12589’s first flight “inherits” VKO, the last arrival of member 2969. Any time difference computed this way would be nonsense for the first row of every member. With PARTITION BY member_id, each member’s first row correctly gets NULL.

Time between events

Subtract LAG of a timestamp from the timestamp and you get the time since the previous event. The first flights of member 12589, with hours since the previous departure:

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.departure_airport, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tm.member_id = 12589
)
SELECT scheduled_departure, departure_airport AS dep, arrival_airport AS arr,
       LAG(arrival_airport) OVER (ORDER BY scheduled_departure) AS prev_arr,
       ROUND(EXTRACT(EPOCH FROM scheduled_departure
             - LAG(scheduled_departure) OVER (ORDER BY scheduled_departure)) / 3600, 1) AS hours_since_prev
FROM trips
ORDER BY scheduled_departure
LIMIT 6;

Timestamp minus timestamp is an INTERVAL; EXTRACT(EPOCH FROM ...) turns it into seconds, which are easier to average and compare. For dates, date - date is already a whole number of days in PostgreSQL and DuckDB. More on this in SQL date functions.

The second flight starts where the first one landed (ESL) 4.6 hours later: a connection. Put the rule into a filter and count connections per airport across all members. The layover is the time from the previous scheduled arrival to this departure, and the window function is computed in a CTE because it cannot go in WHERE:

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.scheduled_arrival,
         f.departure_airport, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
), legs AS (
  SELECT member_id, departure_airport,
         LAG(arrival_airport) OVER w AS prev_arr,
         EXTRACT(EPOCH FROM scheduled_departure - LAG(scheduled_arrival) OVER w) / 60 AS layover_min
  FROM trips
  WINDOW w AS (PARTITION BY member_id ORDER BY scheduled_departure)
)
SELECT departure_airport AS hub, COUNT(*) AS connections,
       MIN(layover_min) AS min_layover, ROUND(AVG(layover_min)) AS avg_layover
FROM legs
WHERE prev_arr = departure_airport AND layover_min < 360
GROUP BY departure_airport
ORDER BY connections DESC
LIMIT 5;

WINDOW w AS (...) names a window once and reuses it in several functions; PostgreSQL, DuckDB and MySQL 8 support it, and SQL Server has it from version 2022.

What happens next: LEAD on events

LEAD answers “what did the user do right after X?”. The next event after select_fare in the app, with the median time to it:

WITH e AS (
  SELECT client_id, event_time, event_name,
         LEAD(event_name) OVER w AS next_event,
         EXTRACT(EPOCH FROM LEAD(event_time) OVER w - event_time) AS secs_to_next
  FROM app_events
  WINDOW w AS (PARTITION BY client_id ORDER BY event_time, event_id)
)
SELECT COALESCE(next_event, '(none)') AS next_event,
       COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY secs_to_next) AS median_secs
FROM e
WHERE event_name = 'select_fare'
GROUP BY 1
ORDER BY n DESC;

86.4% go on to add a passenger, typically within a minute. (none) means the client never did anything after that event in the data. And select_fare followed by select_fare after 0 seconds is a red flag: those are duplicate events, which should be removed first (see ROW_NUMBER vs RANK vs DENSE_RANK for the deduplication pattern). Note the tie-breaker event_id in the window order: with two events at the same second, “next” would otherwise be arbitrary.

Detecting changes and sessions

LAG works on any type, not just numbers. Compare a member’s tier with the previous one to see every upgrade and downgrade:

SELECT member_id, tier, valid_from,
       LAG(tier)        OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier,
       LEAD(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from) AS next_change
FROM loyalty_tier_history
WHERE member_id = 12589
ORDER BY valid_from;

Aggregated over all members, the same LAG gives a transition table:

WITH h AS (
  SELECT member_id, tier, valid_from,
         LAG(tier) OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier
  FROM loyalty_tier_history
)
SELECT prev_tier, tier, COUNT(*) AS moves
FROM h
WHERE prev_tier IS NOT NULL AND prev_tier <> tier
GROUP BY prev_tier, tier
ORDER BY moves DESC;

LEAD(valid_from) in the previous query is the basis of history checks: the next version should start exactly when this one ends. That check, with the overlaps it finds in this table, is in the SCD type 2 guide.

Sessions. A common rule: a new session starts when more than 30 minutes have passed since the user’s previous event. Flag that with LAG, then a running SUM of the flags numbers the sessions:

WITH e AS (
  SELECT client_id, event_time,
         CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY client_id ORDER BY event_time)
                   <= INTERVAL '30 minutes' THEN 0 ELSE 1 END AS new_session
  FROM app_events
  WHERE client_id = '00017a7b4ac7'
)
SELECT event_time, new_session,
       SUM(new_session) OVER (PARTITION BY client_id ORDER BY event_time
                              ROWS UNBOUNDED PRECEDING) AS session_no
FROM e
ORDER BY event_time
LIMIT 8;

The first event has a NULL gap, the comparison is not true, and the ELSE 1 correctly opens session 1. The same flag-and-sum idea finds streaks of consecutive days and gaps between them; that family of problems is covered in gaps and islands in SQL.

Common mistakes

• Using LAG in WHERE. WHERE LAG(status) OVER (...) = 'Cancelled' fails (“WHERE clause cannot contain window functions”). Compute it in a CTE, filter outside.
• Filtering before the window. A WHERE on the date range removes the rows LAG would need; the first row of your range then shows NULL. If you need the previous value for the first day, compute LAG over a wider range and filter in the outer query.
• Forgetting PARTITION BY. The first row of each entity takes the value from the previous entity.
• Non-unique ORDER BY. If two rows have the same timestamp, which one is “previous” is undefined. Add a tie-breaker.
• Assuming rows are periods. LAG(x, 7) is seven rows, not seven days. Fill missing dates first.
• Expecting IGNORE NULLS everywhere. To get the last non-NULL value, Oracle and Snowflake accept LAG(x) IGNORE NULLS OVER (...) and DuckDB LAG(x IGNORE NULLS) OVER (...), but PostgreSQL has no IGNORE NULLS. The portable workaround: COUNT(x) OVER (ORDER BY t) gives a group number that grows only on non-NULL rows, then MAX(x) OVER (PARTITION BY that_group) fills the value forward.

FAQ

What is the difference between LAG and LEAD in SQL?

LAG returns a value from a row before the current one, LEAD from a row after it, in the order defined by OVER (ORDER BY ...). LEAD(x) OVER (ORDER BY t) is the same as LAG(x) OVER (ORDER BY t DESC).

How do I get the previous row’s value in SQL?

LAG(column) OVER (ORDER BY sort_column). Add PARTITION BY if the rows belong to different users, products or accounts, so that each one’s first row gets NULL instead of another entity’s value.

How do I calculate the change from the previous month?

Aggregate to one row per month, then value - LAG(value) OVER (ORDER BY month) for the absolute change and 100.0 * (value - LAG(value) OVER (ORDER BY month)) / LAG(value) OVER (ORDER BY month) for the percentage. Exclude incomplete months.

How do I replace NULL in LAG?

Pass a third argument: LAG(value, 1, 0) returns 0 when there is no previous row. Or wrap it: COALESCE(LAG(value) OVER (...), 0). The difference: COALESCE also replaces a NULL that is stored in the previous row, the default argument does not.

Does MySQL support LAG and LEAD?

Yes, from MySQL 8.0. SQL Server has them from 2012, and PostgreSQL, DuckDB, Oracle, BigQuery and Snowflake all support them with the same syntax.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文