Looking Back and Forward: LAG() and LEAD() in SQL

Most window functions look at the current row and summarise its neighbours. LAG() and LEAD() do something more direct: they reach out and grab a value from a specific neighbouring row: the one just before, or just after, the current row in the window's ordering. That single ability turns awkward self-joins into a one-line expression, and it is the foundation of nearly every "compare this row to the previous one" query you will ever write.

A Different Kind of Window Function

The ranking functions answer the question "where does this row stand?". Aggregates with OVER answer "what is the total across these related rows?". LAG() and LEAD() answer a third kind of question entirely: "what was the value in the row next to this one?". They belong to a family often called value functions (or offset functions): functions that fetch a value from another row in the partition rather than computing something new.

The two are perfect mirror images. LAG() looks backward, toward earlier rows in the ordering. LEAD() looks forward, toward later rows. Everything else about them is identical: the same arguments, the same rules, the same behaviour at the edges of the partition. Learn one and you have learned both.

The Sample Dataset

LAG() and LEAD() are at their most natural over an ordered sequence, so for this article we use a small table of monthly sales figures. Each row is one month's revenue; the rows are meant to be read in calendar order, which is exactly the order these functions will walk through.

monthly_sales

Six rows are enough to show every behaviour that matters: a clear upward trend, one month that dips, one pair of months that stay flat, and, crucially, a first and a last row, where a backward or forward look runs off the edge of the data.

LAG(): Reaching Back to an Earlier Row

LAG() returns a value from a row that comes before the current one, at a fixed offset. Its full signature takes three arguments, two of which are optional:

• expression: the column or expression whose value you want to pull from the earlier row.
• offset: how many rows back to look. Defaults to 1 (the immediately preceding row).
• default: the value to return when there is no row at that offset (i.e. you fall off the start of the partition). Defaults to NULL.

Unlike some of the aggregate window functions, the ORDER BY inside OVER is what gives these functions meaning: it defines what "previous" and "next" actually refer to. Exact requirements vary by engine, but you should always specify an ORDER BY with LAG() and LEAD(): without a defined order, "the previous row" is arbitrary, and the result is not something you can rely on. Let's pull each month's revenue alongside the revenue of the month before it:

Every prev_revenue value is simply the revenue from the row directly above it. February sees January's 1000.00, March sees February's 1200.00, and so on. The very first row, January, has no earlier row to reach (there is nothing before it), so LAG() returns NULL. That NULL at the boundary is not an error; it is the honest answer to "what came before the first row?"

LEAD(): Reaching Forward to a Later Row

LEAD() is the exact mirror of LAG(). Same three arguments, same rules, but it looks in the opposite direction, pulling a value from a row that comes after the current one:

Ask for each month's revenue alongside the revenue of the month that follows it:

This time the boundary NULL moves to the opposite end. January sees February's 1200.00, February sees March's 1100.00, and so on, but June, the last row, has no month after it, so its next_revenue is NULL. Where LAG() leaves the first row empty, LEAD() leaves the last row empty. Same idea, opposite edge.

The offset and default Arguments

The two optional arguments are where LAG() and LEAD() gain their real flexibility. The second argument, offset, changes how far the function reaches. LAG(revenue, 2) looks two rows back instead of one. One subtlety worth internalising: the offset counts rows, not calendar periods. If your data has exactly one row per month with no gaps, then two rows back is indeed two months earlier, but if a month is missing, "two rows back" and "two months earlier" are no longer the same thing. The function counts positions in the ordered result, nothing more.

The third argument, default, replaces the NULL that would otherwise appear at the boundary. Instead of an empty cell when there is no earlier row, you can substitute a value that makes sense for your calculation, often 0. Here we look two months back and fall back to 0 at the edges:

Now the first two rows fall off the edge: there is no row two positions before January or February, so both would normally be NULL. Because we supplied 0 as the default, they show 0.00 instead. From March onward, the function reaches a real row two months back: March sees January, April sees February, and the pattern continues.

PARTITION BY: Previous Row Within Each Group

So far every example has treated the table as one long sequence. In real queries you usually want the "previous row" to reset at the boundary of some group: the previous month for each region, the previous order for each customer, and so on. That is exactly what PARTITION BY does: it splits the rows into independent sequences, and LAG() and LEAD() never reach across the boundary from one partition into another.

To show this, here is a slightly wider dataset: the same monthly figures, now split across two regions:

regional_sales

Look at the first row of the South partition (2024-01). Even though a North row physically precedes it in the table, its prev_revenue is NULL, not North's March figure. PARTITION BY region gives each region its own private sequence, so the backward look stops at the start of that region rather than spilling into the previous group. Without the partition, South's January would have incorrectly reached back to North's last row, a classic and hard-to-spot bug.

The Classic Use Case: Period-over-Period Change

The single most common reason to reach for LAG() is to compare each row against the previous one: month-over-month growth, day-over-day change, this reading versus the last. Once the previous value sits on the same row as the current one, the comparison is just ordinary arithmetic. Here we compute both the absolute and the percentage change from the previous month:

Each change is this month's revenue minus last month's; each pct_change expresses that same difference as a percentage of the previous month. The story reads straight off the table: a 20% jump into February, a dip in March, a strong recovery in April, a flat May (zero change, the two 1500.00 months), and another climb into June. January is NULL in both computed columns because there is no earlier month to compare against, and any arithmetic involving that boundary NULL is itself NULL.

That query repeats LAG(revenue) OVER (ORDER BY month) three times, which is correct but hard to read. A database optimizer may recognise the identical window expressions and avoid redundant work, but that is not something to rely on as a rule; the execution plan is the way to confirm it. For readability, the cleaner approach is to compute LAG() once in a CTE and do the arithmetic in an outer query:

This produces exactly the same result as the version above, but computes the previous value once and names it. Many engines also offer a WINDOW clause that lets you define the window once and reference it by name, another good way to keep longer expressions tidy.

Another Use Case: Detecting Changes Between Rows

Because LAG() puts the previous value right next to the current one, spotting where something changed becomes a simple comparison. Suppose you only care about the months where revenue moved at all: comparing each row's revenue against the previous row's tells you immediately:

Notice the query computes LAG() in an inner subquery and then uses its result, prev_revenue, in the CASE expression of the outer query. That structure is not just for readability; it is required, for a reason worth understanding in its own right.

A Use Case for LEAD(): Time Until the Next Event

LAG() shines at period-over-period comparisons, but LEAD() has natural problems of its own, anything phrased as "how long until the next…". Given a log of events per user, LEAD() can look forward to the next event and measure the gap to it. Here we find, for each login, when that user next logged in:

logins

Each row now knows when the same user's next login happened, and subtracting the two timestamps gives the gap between sessions, a natural building block for questions like "average time between logins" or "which users went longest without returning." The PARTITION BY user_id keeps each user's timeline separate, and the last login for each user has no successor, so its next_login is NULL. This is the kind of forward-looking question LAG() simply cannot answer, which is exactly why LEAD() exists as its own function rather than a footnote.

Common Pitfalls

• Omitting ORDER BY inside OVER. For ranking and aggregate functions, ORDER BY is sometimes optional. For LAG() and LEAD() it is what defines "the previous row" in the first place: leave it out and the neighbour you get back is arbitrary. Always specify the sequence explicitly.
• Expecting the default to replace data NULLs. The third argument only fills the gap at the partition boundary, where no row exists at the requested offset. A NULL that is genuinely present in your source column passes straight through; if you need to handle those, do it separately with COALESCE() on the expression.
• Not handling the boundary NULL in arithmetic. When LAG() returns NULL at the first row, any expression built on it (revenue - prev, a percentage, a ratio) becomes NULL too. That is usually correct, but if you want a concrete value there instead, supply a sensible default (such as 0) as the third argument, or wrap the result in COALESCE().
• Trying to filter on LAG()/LEAD() directly in WHERE. Like every window function, these are computed after WHERE runs, so you cannot reference the result in the same query's WHERE clause. This raises an error:
  SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev FROM monthly_sales WHERE revenue > prev; -- ERROR: prev does not exist yet at this stage
  SQL's logical processing order is FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY. Because WHERE runs before window functions are evaluated, it cannot see prev. The fix is always the same: compute the window function in a subquery or CTE, then filter on it in the outer query, exactly the structure used in the change-detection example above.
• Ordering by a non-unique column and expecting a stable neighbour. If two rows tie on the ORDER BY expression, which one counts as "previous" is not guaranteed. When it matters, add a unique tiebreaker column to the ordering so the sequence is deterministic.

Cross-Database Support

LAG() and LEAD() are standard SQL window functions and are widely available. PostgreSQL, MySQL 8.0 and later, Microsoft SQL Server (since SQL Server 2012), SQLite 3.25 and later, Oracle, and Amazon Redshift all support them, and the basic three-argument form (expression, offset, default) works the same way across them, including the behaviour at partition boundaries. Beyond that basic form, dialects diverge. Some engines support an optional { RESPECT | IGNORE } NULLS modifier that changes how the offset is counted: RESPECT NULLS (the default everywhere) counts a NULL-valued row like any other, so LAG(revenue) can return a NULL simply because the previous row's revenue was NULL; IGNORE NULLS instead skips past NULL-valued rows and keeps looking until it finds a real value, handy when your series has gaps and you want "the last row that actually had a reading." Its availability and exact syntax vary from one engine to another, though, so if you need anything past the everyday three-argument call, it is worth checking your specific engine's documentation rather than assuming portability.

LAG() vs LEAD(): Quick Reference

The two functions are symmetric, so a side-by-side summary is the fastest way to keep them straight:

And the effect of the offset argument, at a glance:

When to Reach for LAG() and LEAD()

Any time a question involves the words "previous," "next," "compared to last," or "change since," LAG() and LEAD() are almost certainly the right tool. When all you need is a fixed offset from the current row, they often eliminate the need for a self-join (joining a table to itself on month = month - 1, with all the fragility that brings), replacing it with a single, declarative expression that reads in the natural order of the data. (Self-joins still earn their place for more complex row relationships; these functions simply handle the common "look at the neighbour" case far more cleanly.)

• Reach for LAG() when the comparison looks backward: growth over the previous period, the gap since the last event, whether a value rose or fell from before.
• Reach for LEAD() when it looks forward: the time until the next event, what comes after the current row, whether a value is about to change.

Both share the same signature, the same mandatory ORDER BY, and the same graceful behaviour at the edges of the data. Master the three arguments (expression, offset, default) and you have a precise, readable way to let any row see its neighbours.