Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL LAG() and LEAD() functions

Reference for SQL LAG() and LEAD(): read a value from a previous or following row, offsets, default values, partitions, NULL at the edges and change calculations.

LAG() and LEAD() are window functions that read a column from another row in the same partition without a self join. LAG(expr) looks backward at the previous row in the window order; LEAD(expr) looks forward at the next row. A second argument chooses how many rows to move, and a third supplies a default for when there is no such row.

They are the natural tool for "difference from the previous period", "time since the last event", "did the value change" and "what comes next". Because they use the ORDER BY of the window, the meaning of "previous" is always defined by you.

SQL
SELECT id, ordered_at,
       LAG(ordered_at)  OVER (ORDER BY ordered_at) AS previous_order,
       LEAD(ordered_at) OVER (ORDER BY ordered_at) AS next_order
FROM orders;

Try it

SQL Previous and next salary in a department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Days between a customer's orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Offsets of 2 and a default value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The last row says none only because the WHERE id <= 4 filter removed the products that would follow it; window functions see the rows left after WHERE.

Syntax

SQL
LAG(expression [, offset [, default]]) OVER (
  [ PARTITION BY ... ] ORDER BY ...
)
LEAD(expression [, offset [, default]]) OVER (
  [ PARTITION BY ... ] ORDER BY ...
)
Part Description
expression The value to read from the other row: a column or any expression.
offset Optional. How many rows back (LAG) or forward (LEAD). Default 1. A non-negative integer; 0 means the current row.
default Optional. Returned when the target row is outside the partition. Default NULL.
ORDER BY Required in practice: it defines "previous" and "next".

Returns: the value of expression at the target row, with the type of expression, or default / NULL if there is no such row. A frame clause is not allowed: these functions ignore frames and always see the whole partition.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
LAG, LEAD 3.25+ 8.4+ 8.0+ 2012+ Yes
IGNORE NULLS / RESPECT NULLS No No No 2022+ Yes
Offset 0 allowed Yes Yes Yes Yes Yes
SQL
-- Oracle, SQL Server 2022: skip NULLs when looking back
SELECT day, LAG(reading) IGNORE NULLS OVER (ORDER BY day) AS last_known FROM sensor;

Where IGNORE NULLS is missing, emulate it with a correlated subquery or a running MAX() of a marker, as taught in the lag and lead lesson.

Usage notes

  • The edges are NULL. The first row has no LAG and the last has no LEAD. Any arithmetic with that NULL is also NULL, so use COALESCE() or the third argument.
  • A NULL stored in the data is indistinguishable from "no row" unless you use the default argument carefully or test the row number.
  • Partitions are walls. Add PARTITION BY so the first order of customer 8 does not look back at the last order of customer 3.
  • Order matters, and ties need a tie-breaker. Add a unique column to the ORDER BY, or the "previous" row among equal values is arbitrary.
  • Change detection: value <> LAG(value) OVER (...) is true when a value changed. It is NULL on the first row, so wrap with COALESCE(..., 1) or handle it separately.
  • Filtering on the result requires an outer query, because window functions are not allowed in WHERE.
  • Replaces self joins. One LAG() is shorter and usually faster than joining a table to itself on id - 1, which also breaks when ids have gaps.
  • offset must not be negative in most databases; use the opposite function instead.