# Window functions

> 13 Window functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

13 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

| Function | Summary |
| --- | --- |
| [`cume_dist`](#cume_dist) | Measures the fraction of ordered partition rows at or before the current peer group. |
| [`dense_rank`](#dense_rank) | Assigns consecutive peer-group numbers without gaps after ties. |
| [`first_value`](#first_value) | Reads the earliest value within the current ordered window frame. |
| [`lag`](#lag) | Returns a value from an earlier partition row with an optional missing-row default. |
| [`lag_in_frame`](#lag_in_frame) | Reads an earlier row only when it remains inside the declared window frame. |
| [`last_value`](#last_value) | Retrieves the value at the ending row of the active window frame. |
| [`lead`](#lead) | Looks ahead within an ordered partition and substitutes a default beyond its end. |
| [`lead_in_frame`](#lead_in_frame) | Fetches a later value while honoring the current frame’s upper boundary. |
| [`nth_value`](#nth_value) | Selects a one-based position from the current frame, returning NULL if it is missing. |
| [`ntile`](#ntile) | Partitions ordered rows into nearly equal numbered buckets. |
| [`percent_rank`](#percent_rank) | Scales a row’s gapped rank onto the partition-relative interval from zero to one. |
| [`rank`](#rank) | Numbers ordered peers by their first position, leaving gaps after tied rows. |
| [`row_number`](#row_number) | Gives every row a unique sequential position inside its ordered partition. |

## cume_dist

Measures the fraction of ordered partition rows at or before the current peer group.

```text
cume_dist() -> float64
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/nullary` |
| Returns | `float64` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL ordering keys participate according to NULLS FIRST/LAST; result non-NULL |

Divides the number of rows preceding or tied with the current row by the partition size. NULL sort keys participate under NULLS FIRST or NULLS LAST, and the result is non-NULL with a singleton partition returning one. General partition materialization is one-shot only; incompatible continuous use raises INVALID_ARGUMENT and admitted state limits raise RESOURCE_LIMIT.

**Example**

```sql
cume_dist() OVER (ORDER BY 7)
-- → 1
```

## dense_rank

Assigns consecutive peer-group numbers without gaps after ties.

```text
dense_rank() -> uint64
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/nullary` |
| Returns | `uint64` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL ordering keys participate according to NULLS FIRST/LAST; result non-NULL |

Equal ordering keys receive the same rank, and the next distinct key advances the value by exactly one. NULL keys join peer groups according to the explicit NULL ordering, while the uint64 result itself is never NULL. A total admitted ordering is required, and this one-shot window contract rejects continuous execution with INVALID_ARGUMENT.

**Example**

```sql
dense_rank() OVER (ORDER BY 7)
-- → 1
```

## first_value

Reads the earliest value within the current ordered window frame.

```text
first_value(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/unary+generic` |
| Returns | `T` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Selects the first frame row rather than the first non-NULL value, so a selected SQL NULL is preserved. An empty frame returns NULL and output retains the expression’s logical type. Deterministic selection requires sufficient ordering, and general frame evaluation is one-shot only with INVALID_ARGUMENT for incompatible continuous admission.

**Example**

```sql
first_value(12) OVER (
  ORDER BY 7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- → 12
```

## lag

Returns a value from an earlier partition row with an optional missing-row default.

```text
lag(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lag(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lag(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/1..3-ary+generic` |
| Returns | `T` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Moves backward by the nonnegative offset, defaulting to one, independently of the declared frame. A missing predecessor yields the supplied default or NULL, while an existing predecessor containing NULL keeps NULL. Offset zero reads the current row; incompatible default types raise TYPE_MISMATCH, and this one-shot contract rejects incompatible continuous execution with INVALID_ARGUMENT.

**Example**

```sql
lag(12, 1, 0) OVER (ORDER BY 7)
-- → 0
```

## lag_in_frame

Reads an earlier row only when it remains inside the declared window frame.

```text
lag_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lag_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lag_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/1..3-ary+generic` |
| Returns | `T` |
| Aliases | `lagInFrame` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Applies a backward offset of one by default and checks both partition position and current frame boundaries. A target outside the frame returns the supplied default or NULL, while a present NULL value remains NULL. TYPE_MISMATCH rejects an incompatible default; general frame-aware execution is one-shot only and incompatible streaming raises INVALID_ARGUMENT.

**Example**

```sql
lag_in_frame(12, 1, 0) OVER (
  ORDER BY 7 ROWS BETWEEN CURRENT ROW AND CURRENT ROW
)
-- → 0
```

## last_value

Retrieves the value at the ending row of the active window frame.

```text
last_value(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/unary+generic` |
| Returns | `T` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Respects the declared frame boundary, which may end at the current row rather than the partition’s last row. A selected NULL or an empty frame yields NULL; values are not implicitly skipped. The contract requires one-shot frame materialization, and incompatible streaming or invalid framing raises INVALID_ARGUMENT.

**Example**

```sql
last_value(12) OVER (
  ORDER BY 7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- → 12
```

## lead

Looks ahead within an ordered partition and substitutes a default beyond its end.

```text
lead(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lead(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lead(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/1..3-ary+generic` |
| Returns | `T` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Advances by the optional nonnegative offset, which defaults to one and is independent of frame boundaries. A missing successor returns the provided default or NULL; an existing NULL successor is not replaced. Incompatible default types raise TYPE_MISMATCH, and one-shot partition evaluation rejects continuous admission with INVALID_ARGUMENT.

**Example**

```sql
lead(12, 1, 99) OVER (ORDER BY 7)
-- → 99
```

## lead_in_frame

Fetches a later value while honoring the current frame’s upper boundary.

```text
lead_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lead_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lead_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/1..3-ary+generic` |
| Returns | `T` |
| Aliases | `leadInFrame` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Looks forward by the nonnegative offset, defaulting to one, but never crosses the active frame. The default is used only when that target row is unavailable, not when its value is NULL. Incompatible defaults raise TYPE_MISMATCH, and the one-shot window contract raises INVALID_ARGUMENT for incompatible continuous plans.

**Example**

```sql
lead_in_frame(12, 1, 0) OVER (
  ORDER BY 7 ROWS BETWEEN CURRENT ROW AND CURRENT ROW
)
-- → 0
```

## nth_value

Selects a one-based position from the current frame, returning NULL if it is missing.

```text
nth_value(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],n:uint64) -> T
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/binary+generic` |
| Returns | `T` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | respect NULL input values; missing row yields supplied default or NULL |

Reads the nth frame row without ignoring NULL expression values, and preserves the selected logical type. A position beyond the frame length returns NULL, while zero raises INVALID_ARGUMENT. The current contract is one-shot only; invalid continuous admission raises INVALID_ARGUMENT and excessive retained frame data raises RESOURCE_LIMIT.

**Example**

```sql
nth_value(12, 2) OVER (
  ORDER BY 7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- → NULL
```

## ntile

Partitions ordered rows into nearly equal numbered buckets.

```text
ntile(buckets:uint64) -> uint64
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/unary` |
| Returns | `uint64` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL ordering keys participate according to NULLS FIRST/LAST; result non-NULL |

Assigns bucket numbers from one, giving earlier buckets an extra row when division is uneven. NULL ordering keys remain rows in the distribution and do not make the result NULL; zero bucket count raises INVALID_ARGUMENT. The complete partition must be available under this one-shot contract, and excessive materialization raises RESOURCE_LIMIT.

**Example**

```sql
ntile(4) OVER (ORDER BY 7)
-- → 1
```

## percent_rank

Scales a row’s gapped rank onto the partition-relative interval from zero to one.

```text
percent_rank() -> float64
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/nullary` |
| Returns | `float64` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL ordering keys participate according to NULLS FIRST/LAST; result non-NULL |

For partitions larger than one row, computes rank minus one divided by partition size minus one; a singleton returns zero. Peer and NULL-key ordering follow the window order, and the floating result is never NULL. One-shot partition evaluation is required, with INVALID_ARGUMENT for incompatible streaming and RESOURCE_LIMIT for excessive retained rows.

**Example**

```sql
percent_rank() OVER (ORDER BY 7)
-- → 0
```

## rank

Numbers ordered peers by their first position, leaving gaps after tied rows.

```text
rank() -> uint64
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/nullary` |
| Returns | `uint64` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL ordering keys participate according to NULLS FIRST/LAST; result non-NULL |

Returns one plus the count of rows strictly preceding the current peer group. Ties share a rank and consume positions, while NULL ordering keys are handled by NULLS FIRST or NULLS LAST without making the result NULL. The general partition contract is one-shot only; invalid continuous admission raises INVALID_ARGUMENT and excessive window state raises RESOURCE_LIMIT.

**Example**

```sql
rank() OVER (ORDER BY 7)
-- → 1
```

## row_number

Gives every row a unique sequential position inside its ordered partition.

```text
row_number() -> uint64
```

| | |
| --- | --- |
| Kind | window |
| Signature class | `window/nullary` |
| Returns | `uint64` |
| Streaming | One-shot only. |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL ordering keys participate according to NULLS FIRST/LAST; result non-NULL |

Counts rows from one, including rows whose ordering keys are NULL, and always returns a non-NULL uint64. Tied keys need an explicit deterministic tiebreaker when repeatable numbering matters. This contract requires a finite one-shot partition; incompatible streaming raises INVALID_ARGUMENT and materialization beyond the admitted budget raises RESOURCE_LIMIT.

**Example**

```sql
row_number() OVER (ORDER BY 7)
-- → 1
```

---

Source: https://nyxdb.io/docs/functions/window/