# Window functions

Source: https://www.hotdata.dev/docs/sql-functions-window
Site index: https://www.hotdata.dev/llms.txt

Reference for window functions in [HotSQL](/docs/sql). Names, signatures, and examples match the engine exactly.

### cume_dist

Relative rank of the current row: (number of rows preceding or peer with the current row) / (total rows).

```
cume_dist()
```

```sql
-- Example usage of the cume_dist window function:
SELECT salary,
    cume_dist() OVER (ORDER BY salary) AS cume_dist
FROM employees;

+--------+-----------+
| salary | cume_dist |
+--------+-----------+
| 30000  | 0.33      |
| 50000  | 0.67      |
| 70000  | 1.00      |
+--------+-----------+
```

### dense_rank

Rank of the current row within its partition, without gaps. Rows with equal ORDER BY values share a rank and the next rank is consecutive.

```
dense_rank()
```

**Related:** `rank`

### first_value

Value of `expression` from the first row of the window frame.

```
first_value(expression)
```

**Arguments**

- `expression`: Expression to evaluate.

**Related:** `last_value`, `nth_value`

### lag

Value of `expression` from a row `offset` positions before the current row in the partition (offset defaults to 1). Returns `default` (or NULL) when out of range.

```
lag(expression [, offset [, default]])
```

**Arguments**

- `expression`: Expression to evaluate on the offset row.
- `offset`: Number of rows back. Default 1.
- `default`: Value returned when the offset is out of range.

**Related:** `lead`

### last_value

Value of `expression` from the last row of the window frame.

```
last_value(expression)
```

**Arguments**

- `expression`: Expression to evaluate.

**Related:** `first_value`, `nth_value`

### lead

Value of `expression` from a row `offset` positions after the current row in the partition (offset defaults to 1). Returns `default` (or NULL) when out of range.

```
lead(expression [, offset [, default]])
```

**Arguments**

- `expression`: Expression to evaluate on the offset row.
- `offset`: Number of rows forward. Default 1.
- `default`: Value returned when the offset is out of range.

**Related:** `lag`

### nth_value

Value of `expression` from the nth row (1-based) of the window frame.

```
nth_value(expression, n)
```

**Arguments**

- `expression`: Expression to evaluate.
- `n`: 1-based row position within the frame.

**Related:** `first_value`, `last_value`

### ntile

Integer ranging from 1 to the argument value, dividing the partition as equally as possible

```
ntile(expression)
```

**Arguments**

- `expression`: An integer describing the number groups the partition should be split into

```sql
-- Example usage of the ntile window function:
SELECT employee_id,
    salary,
    ntile(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

+-------------+--------+----------+
| employee_id | salary | quartile |
+-------------+--------+----------+
| 1           | 90000  | 1        |
| 2           | 85000  | 1        |
| 3           | 80000  | 2        |
| 4           | 70000  | 2        |
| 5           | 60000  | 3        |
| 6           | 50000  | 3        |
| 7           | 40000  | 4        |
| 8           | 30000  | 4        |
+-------------+--------+----------+
```

### percent_rank

Relative rank of the current row: (rank - 1) / (total partition rows - 1).

```
percent_rank()
```

**Related:** `rank`, `cume_dist`

### rank

Rank of the current row within its partition, with gaps. Rows with equal ORDER BY values share a rank; the next rank skips ahead.

```
rank()
```

**Related:** `dense_rank`, `percent_rank`

### row_number

Number of the current row within its partition, counting from 1.

```
row_number()
```

```sql
-- Example usage of the row_number window function:
SELECT department,
  salary,
  row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

+-------------+--------+---------+
| department  | salary | row_num |
+-------------+--------+---------+
| Sales       | 70000  | 1       |
| Sales       | 50000  | 2       |
| Sales       | 50000  | 3       |
| Sales       | 30000  | 4       |
| Engineering | 90000  | 1       |
| Engineering | 80000  | 2       |
+-------------+--------+---------+
```