lag, lead

On this page

Reads a value at an offset from the current row in the window's sort order.

Syntax

lag(<expr> [, offset => <offset>] [, default => <default>])
lead(<expr> [, offset => <offset>] [, default => <default>])

Arguments

ArgumentTypeDescription
<expr>Any typeInput value.
offsetintOptional constant; defaults to 1. Non-negative number of rows to move. 0 selects the current row. Must not be NULL.
defaultSame as <expr>Optional constant; defaults to NULL. Value returned when the requested row is outside the window.

Returns

Returns the same type as <expr>.

  • Let i be the current row's position in the order defined by WITHIN GROUP ORDER BY. lag reads position i - offset; lead reads position i + offset.
  • If the requested row exists, returns its value, including NULL.
  • If the requested row is outside the window, returns default.

Usage notes

Use in a WINDOW clause. GROUP BY defines independent groups, and WITHIN GROUP ORDER BY defines the row order.

Examples

Measure the time between session events

For each session event, calculate the time since the previous event and show the next action:

VALUES
    ('s-17', '2026-06-01T10:00:00Z', 'cart_viewed'),
    ('s-17', '2026-06-01T10:00:20Z', 'checkout_started'),
    ('s-17', '2026-06-01T10:01:05Z', 'payment_completed'),
    ('s-42', '2026-06-01T10:00:10Z', 'cart_viewed'),
    ('s-42', '2026-06-01T10:00:40Z', 'checkout_started')
SELECT $0 AS session_id, $1::timestamp AS event_time, $2 AS event
GROUP BY session_id
WITHIN GROUP ORDER BY event_time
WINDOW lag(event_time) AS previous_time, lead(event, default => 'end') AS next_event
ORDER BY session_id, event_time
SELECT session_id, event, event_time - previous_time AS since_previous, next_event
+------------+-------------------+----------------+-------------------+
| session_id | event             | since_previous | next_event        |
+------------+-------------------+----------------+-------------------+
| s-17       | cart_viewed       | NULL           | checkout_started  |
| s-17       | checkout_started  | 20s            | payment_completed |
| s-17       | payment_completed | 45s            | end               |
| s-42       | cart_viewed       | NULL           | checkout_started  |
| s-42       | checkout_started  | 30s            | end               |
+------------+-------------------+----------------+-------------------+

The first event in each session has no previous timestamp. The last event uses the supplied end label for its missing next row.

Compare daily sales

Compare each day with the previous recorded day. The first day has no prior observation, so its change stays NULL.

VALUES ('2026-06-01', 12000), ('2026-06-02', 15000), ('2026-06-03', 13500)
SELECT $0 AS day, $1 AS sales_cents
WITHIN GROUP ORDER BY day
WINDOW lag(sales_cents) AS previous_cents
SELECT day, sales_cents, sales_cents - previous_cents AS change_cents
ORDER BY day
+------------+-------------+--------------+
| day        | sales_cents | change_cents |
+------------+-------------+--------------+
| 2026-06-01 | 12000       | NULL         |
| 2026-06-02 | 15000       | 3000         |
| 2026-06-03 | 13500       | -1500        |
+------------+-------------+--------------+

lag moves by rows, not calendar days. Fill gaps first if the comparison must always mean the previous calendar day.