Window — offset functions (WINDOW LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE)

Syntax

WINDOW LAG(<expr> [, <offset> [, <default>]])  SORT <col> [ASC|DESC] [, ...] [PER <key> [, ...]] AS <col> (Relation)
WINDOW LEAD(<expr> [, <offset> [, <default>]]) SORT <col> [ASC|DESC] [, ...] [PER <key> [, ...]] AS <col> (Relation)
WINDOW FIRST_VALUE(<expr>)    [OVER <frame>]   SORT <col> [ASC|DESC] [, ...] [PER <key> [, ...]] AS <col> (Relation)
WINDOW LAST_VALUE(<expr>)     [OVER <frame>]   SORT <col> [ASC|DESC] [, ...] [PER <key> [, ...]] AS <col> (Relation)
WINDOW NTH_VALUE(<expr>, <n>) [OVER <frame>]   SORT <col> [ASC|DESC] [, ...] [PER <key> [, ...]] AS <col> (Relation)

where <frame> is PARTITION, <n> ROWS, ALL ROWS, or ROWS|RANGE BETWEEN <bound> AND <bound>,
as ROLLING writes it.

WINDOW LAG(revenue, 1)   SORT month ASC PER region     AS prev_revenue (MonthlyRevenue)
WINDOW LEAD(event_time)  SORT event_time ASC PER user_id AS next_time   (ClickEvents)
WINDOW FIRST_VALUE(price) SORT trade_time ASC PER session_id AS open_price (Trades)
WINDOW LAST_VALUE(price) OVER PARTITION SORT trade_time ASC PER session_id AS close_price (Trades)

Description

An offset window reads a value from another row within the same ordered partition and adds it as a new column without collapsing or filtering any rows. The five functions differ only in which row they read:

• LAG(expr, n, default) — the value of expr n rows before the current row in the sort order. At the partition's leading edge (no such row) it emits default, or NULL when default is omitted.
• LEAD(expr, n, default) — the value of expr n rows after the current row. Past the partition's end it emits default, or NULL when omitted.
• FIRST_VALUE(expr) — the value of expr in the first row of the row's frame.
• LAST_VALUE(expr) — the value of expr in the last row of the row's frame.
• NTH_VALUE(expr, n) — the value of expr in the frame's nth row, counting from 1; NULL when the frame is shorter.

The three value functions read a frame, as SQL's do. Without one written, the frame is SQL's default: from the partition's first row to the current row and its peers, the rows equal to it on every sort key (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). So FIRST_VALUE is the partition's first value, but LAST_VALUE is the current row's own value, or its last peer's, and NTH_VALUE(x, 2) is NULL on the first row. This is what PostgreSQL, MySQL, SQL Server, Oracle and SQLite all answer. For the partition's last row on every row, write the whole partition as the frame: LAST_VALUE(price) OVER PARTITION. Any other frame is written after the function as ROLLING writes one: OVER 3 ROWS, OVER ALL ROWS, or OVER ROWS|RANGE BETWEEN … AND ….

For LAG/LEAD the offset n defaults to 1 (the immediately adjacent row) and must be a positive integer literal. FIRST_VALUE/LAST_VALUE take only the value expression. LAG and LEAD count from the current row and take no frame, as in SQL; neither do the ranking functions:

query { WINDOW LAG(v) OVER PARTITION SORT t AS prev (R) };

It is the operator for a value just before or just after in order: the prior status, the price a row replaced, the reading that came next, the one after.

The output schema is the input schema with the AS column appended; its type follows the referenced expression's type (ANY for a complex expression). Every input row is preserved.

Technical Description

Offset functions are the window operator (WindowNode with an OffsetWindow function). Like the ranking and rolling families they add a column without removing any row. The operator buffers each partition, sorts it by the SORT keys, and indexes by position ([bag] materialisation); it is blocking over a provably-unbounded input.

SQL pushdown

When the source is backed by a JDBC connection and the dialect supports window functions (PostgreSQL, DuckDB, SQLite, SQL Server, Db2 and the GENERIC H2 dialect; MySQL is conservative), the offset window is folded into the SQL scan:

SELECT region, month, revenue, LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month ASC) AS prev
FROM monthly_revenue

A σ immediately above an offset window over a pushed source is evaluated in-engine. MongoDB sources evaluate windows in-engine.

FIRST_VALUE, LAST_VALUE and NTH_VALUE are pushed with the frame in force. Without a frame written they have SQL's default, so they are sent with no frame clause, which means the same on every dialect. OVER PARTITION is sent as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, and any other frame as ROLLING's is: as written, with a RANGE offset pushed to PostgreSQL and DuckDB only. SQL Server has no NTH_VALUE, so there it runs in the engine.

LAG/LEAD/NTH_VALUE require a constant, positive-integer offset (no column references). The default value, when given, is evaluated against the partition's first row (constants are the typical case).

Examples

Month-over-month revenue delta (LAG), worked through with real output below:

WithPrev := { WINDOW LAG(revenue, 1) SORT month ASC PER region AS prev_revenue (MonthlyRevenue) };
query { π region, month, revenue, revenue - prev_revenue → mom_delta (WithPrev) };

WithPrev := { WINDOW LAG(revenue, 1) τ month ASC PER region AS prev_revenue (MonthlyRevenue) };
query { π region, month, revenue, revenue - prev_revenue → mom_delta (WithPrev) };

WithPrev := { WINDOW LAG(revenue, 1) SORT month ASC PER region AS prev_revenue (MonthlyRevenue) };
query { PROJECT region, month, revenue, revenue - prev_revenue -> mom_delta (WithPrev) };

The first row of each partition has no predecessor, so prev_revenue is NULL and the arithmetic delta propagates NULL. (Supply a default — LAG(revenue, 1, 0) — to anchor the first delta to 0 instead.)

Time between events / session gap (LEAD over a TIMESTAMP, the substrate for sessionization) — next_time - event_time uses temporal arithmetic and yields a DURATION:

ClickEvents(user_id, event_time):
  7 2026-06-01T10:00:00Z      7 2026-06-01T10:05:00Z      7 2026-06-01T10:40:00Z

WithNext := { WINDOW LEAD(event_time, 1) SORT event_time ASC PER user_id AS next_time (ClickEvents) };
query { π user_id, event_time, next_time - event_time → gap (WithNext) };

WithNext := { WINDOW LEAD(event_time, 1) τ event_time ASC PER user_id AS next_time (ClickEvents) };
query { π user_id, event_time, next_time - event_time → gap (WithNext) };

WithNext := { WINDOW LEAD(event_time, 1) SORT event_time ASC PER user_id AS next_time (ClickEvents) };
query { PROJECT user_id, event_time, next_time - event_time -> gap (WithNext) };

The last event per user has no successor, so its `gap` is `NULL`.

Opening and closing price per trading session (FIRST_VALUE/LAST_VALUE):

WithBounds := {
    WINDOW FIRST_VALUE(price) SORT trade_time ASC PER session_id AS open_price (Trades)
};

WithBounds := {
    WINDOW FIRST_VALUE(price) τ trade_time ASC PER session_id AS open_price (Trades)
};

Every row of a session carries that session's first (opening) price. The closing price is the session's last row, so it reads the whole partition: WINDOW LAST_VALUE(price) OVER PARTITION SORT trade_time ASC PER session_id AS close_price (Trades). Without OVER PARTITION, LAST_VALUE is the latest price so far, which on each row is its own.

Worked Example

The month-over-month question, which is what LAG is for:

MonthlyRevenue := [
| region | month | revenue |
|--------|-------|---------|
| East   | 1     | 100     |
| East   | 2     | 120     |
| East   | 3     | 90      |
| West   | 1     | 80      |
| West   | 2     | 95      |
];

query {
    WINDOW LAG(revenue, 1) SORT month ASC PER region AS prev
        (MonthlyRevenue)
};

MonthlyRevenue := [
| region | month | revenue |
|--------|-------|---------|
| East   | 1     | 100     |
| East   | 2     | 120     |
| East   | 3     | 90      |
| West   | 1     | 80      |
| West   | 2     | 95      |
];

query {
    WINDOW LAG(revenue, 1) τ month ASC PER region AS prev
        (MonthlyRevenue)
};

 region  month  revenue  prev
 ──────  ─────  ───────  ────
 East        1      100  NULL
 East        2      120   100
 East        3       90   120
 West        1       80  NULL
 West        2       95    80
(5 rows)

WINDOW LAG(revenue, 1) SORT month↑ PER region AS prev  ~5 rows
└─ Scan MonthlyRevenue  ~5 rows

Each row now carries the previous month's revenue from its own region. Month 1 has no previous row, so prev is NULL — and note that West's month 1 does not read East's month 3: PER region stops the window at the partition edge rather than running off the end of one partition into the next.

Projecting the difference is the usual next step:

query {
    π region, month, revenue - prev → change (
        WINDOW LAG(revenue, 1) SORT month ASC PER region AS prev
            (MonthlyRevenue))
};

query {
    π region, month, revenue - prev → change (
        WINDOW LAG(revenue, 1) τ month ASC PER region AS prev
            (MonthlyRevenue))
};

query {
    PROJECT region, month, revenue - prev -> change (
        WINDOW LAG(revenue, 1) SORT month ASC PER region AS prev
            (MonthlyRevenue))
};

 region  month  change
 ──────  ─────  ──────
 East        1  NULL
 East        2      20
 East        3     -30
 West        1  NULL
 West        2      15
(5 rows)

Project  ~5 rows
└─ WINDOW LAG(revenue, 1) SORT month↑ PER region AS prev  ~5 rows
   └─ Scan MonthlyRevenue  ~5 rows

The rows with no predecessor come out NULL rather than as a change of zero, which is the honest answer: there is no previous month to have changed from. Give LAG a third argument (LAG(revenue, 1, 0)) if a zero suits the report better.

The frame a value function reads. A session's closing price is its last trade, the last row of the whole partition. LAST_VALUE without a frame reads SQL's default, which ends at the current row, so it gives each row's own price; OVER PARTITION gives the session's close on every row:

Trades := [
| session | t | price |
|---------|---|-------|
| s1      | 1 | 10    |
| s1      | 2 | 12    |
| s1      | 3 | 11    |
| s2      | 1 | 20    |
| s2      | 2 | 21    |
];

query {
    τ session, t (
        WINDOW LAST_VALUE(price) OVER PARTITION SORT t PER session AS close (
            WINDOW LAST_VALUE(price) SORT t PER session AS so_far (Trades)))
};

Trades := [
| session | t | price |
|---------|---|-------|
| s1      | 1 | 10    |
| s1      | 2 | 12    |
| s1      | 3 | 11    |
| s2      | 1 | 20    |
| s2      | 2 | 21    |
];

query {
    τ session, t (
        WINDOW LAST_VALUE(price) OVER PARTITION τ t PER session AS close (
            WINDOW LAST_VALUE(price) τ t PER session AS so_far (Trades)))
};

Trades := [
| session | t | price |
|---------|---|-------|
| s1      | 1 | 10    |
| s1      | 2 | 12    |
| s1      | 3 | 11    |
| s2      | 1 | 20    |
| s2      | 2 | 21    |
];

query {
    SORT session, t (
        WINDOW LAST_VALUE(price) OVER PARTITION SORT t PER session AS close (
            WINDOW LAST_VALUE(price) SORT t PER session AS so_far (Trades)))
};

 session  t  price  so_far  close
 ───────  ─  ─────  ──────  ─────
 s1       1     10      10     11
 s1       2     12      12     11
 s1       3     11      11     11
 s2       1     20      20     21
 s2       2     21      21     21
(5 rows)

Sort  ~5 rows
└─ WINDOW LAST_VALUE(price) OVER PARTITION SORT t↑ PER session AS close  ~5 rows
   └─ WINDOW LAST_VALUE(price) SORT t↑ PER session AS so_far  ~5 rows
      └─ Scan Trades  ~5 rows

A query translated from SQL's LAST_VALUE(price) OVER (PARTITION BY session ORDER BY t) means so_far; one that adds ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING means close.

Limitations

The added column needs a name the input does not already use. Over a schema-on-read source (JSON, HTTP, MongoDB) there is no declared heading to clash with, so the operator always runs; if a document turns out to carry a field of that name, the added column replaces it.

LAG/LEAD take an optional positive-integer-literal offset (default 1) and an optional default value (default NULL), and no frame; FIRST_VALUE/LAST_VALUE take only the value expression, and an optional frame. The AS column must not clash with an existing input column. SQL pushdown is supported on PostgreSQL, DuckDB, SQLite, SQL Server, Db2 and GENERIC (H2) backends; MySQL and MongoDB always evaluate offset windows in-engine.

Alternatives

For sliding/cumulative aggregates use ROLLING. For per-partition rank columns use Window / Ranking. For one collapsed row per group use γ.

See Also

log source — WINDOW LAG over a web server's access log, among other windowing examples on one real dataset

window-ranking, rolling, sort, group

Notes

WINDOW is a reserved keyword; the function names (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE) are contextual — parsed only inside a WINDOW clause — so they remain usable as column names elsewhere.

Partition pruning (WINDOW-001). A selection that fixes a PER (partition) key to a constant above a window — e.g. σ region = "WEST" (WINDOW LAG(amount) … PER region (Sales)) — is pushed below the operator by the optimizer, so the offset is computed only for the matching partition: WINDOW LAG(amount) … PER region (σ region = "WEST" (Sales)). This is safe because an offset, and a frame, never reach across a partition boundary. Only equalities on a partition key are pushed; predicates on other columns stay as a residual σ above the operator. Look for a WINDOW-001 record in relix optimize --report output.