# lag, lead

[Read this page in HTML](https://www.scopedb.io/docs/reference/functions/window/lag-lead)

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

## Syntax

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

## Arguments

| Argument  | Type             | Description                                                                                                         |
| --------- | ---------------- | ------------------------------------------------------------------------------------------------------------------- |
| `<expr>`  | Any type         | Input value.                                                                                                        |
| `offset`  | `int`            | Optional constant; defaults to 1. Non-negative number of rows to move. 0 selects the current row. Must not be NULL. |
| `default` | Same 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:

```scopeql
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
```

```text
+------------+-------------------+----------------+-------------------+
| 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.

```scopeql
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
```

```text
+------------+-------------+--------------+
| 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.