LAG, LEAD, FIRST_VALUE and NTH_VALUE

LAG and LEAD are window functions that read a value from another row of the same window. lag(revenue) returns the revenue of the row before the current one, and lead(revenue) that of the row after it, in the order set by the window’s ORDER BY. FIRST_VALUE and NTH_VALUE read a value from a fixed position instead: the first row of the window frame, or its nth row. With them a query can set each month next to the month before, count the days since a customer’s previous order or show the product each customer bought first, all without joining the table to itself. They work in PostgreSQL, in MySQL 8.0 and later and in SQLite 3.25 and later, and the examples run in your browser on SQLite 3.49.1.

Looking at the row before and the row after

Here is a shop’s revenue for six months, each month next to the one before it and the one after it:

-- A shop's revenue by month, in rupees.
CREATE TABLE monthly (month TEXT PRIMARY KEY, revenue INTEGER NOT NULL);
INSERT INTO monthly VALUES
  ('2026-01', 52000), ('2026-02', 61000), ('2026-03', 58000),
  ('2026-04', 70000), ('2026-05', 66500), ('2026-06', 79800);

-- Each month next to the month before and the month after.
SELECT month, revenue,
       lag(revenue)  OVER (ORDER BY month)           AS prev_month,
       revenue - lag(revenue) OVER (ORDER BY month)  AS change,
       lead(revenue) OVER (ORDER BY month)           AS next_month
FROM monthly
ORDER BY month;

┌─────────┬─────────┬────────────┬────────┬────────────┐
│  month  │ revenue │ prev_month │ change │ next_month │
├─────────┼─────────┼────────────┼────────┼────────────┤
│ 2026-01 │ 52000   │            │        │ 61000      │
│ 2026-02 │ 61000   │ 52000      │ 9000   │ 58000      │
│ 2026-03 │ 58000   │ 61000      │ -3000  │ 70000      │
│ 2026-04 │ 70000   │ 58000      │ 12000  │ 66500      │
│ 2026-05 │ 66500   │ 70000      │ -3500  │ 79800      │
│ 2026-06 │ 79800   │ 66500      │ 13300  │            │
└─────────┴─────────┴────────────┴────────┴────────────┘

LAG found January’s 52000 for February, so February’s change is 9000, and LEAD found February’s 61000 for January. The first month has no month before it, so its LAG is NULL and so is its change; the last month has no LEAD. Both functions take two more arguments: lag(revenue, 3) reads three rows back, and a third argument replaces the NULL, as in lag(revenue, 1, 0). Nothing has to be numbered or joined first: the window’s ORDER BY alone decides what “before” means, so over ORDER BY month DESC the same LAG would read the following month. With a PARTITION BY, every partition starts afresh, and the first row of each partition has no LAG of its own.

The five functions of this lesson, one line each:

• LAG reads a value from a row before the current one: one row back, unless an offset says more.
• LEAD reads a value from a row after the current one, in the same way.
• FIRST_VALUE reads the value of the first row of the window frame.
• LAST_VALUE reads the last row of the frame; by default that is the current row or its last tie, not the partition’s last row.
• NTH_VALUE reads the nth row of the frame, and NULL while the frame has fewer than n rows.

LAG and LEAD always look at the whole partition, whatever the frame. FIRST_VALUE, LAST_VALUE and NTH_VALUE look only at the frame, which by default runs from the first row of the partition to the current row and its ties. That one difference explains most surprises with these functions.

Growth rates, offsets and defaults, with numbers Pro: opens with a Pro, Premium or Ultimate pass