Window functions

These functions are available in Telemetry SQL. Signatures, aliases, argument descriptions, and examples below follow the vendored engine fork. Examples that name a table assume matching columns in your own table; result grids illustrate values and may differ from the dashboard or JSON display.

For a running total, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For the final value across a whole partition, specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; the default ordered frame stops at the current peer group. Add a stable tie-breaker to ORDER BY when row order matters.

SQL reference · Scalar functions · String and regular expression functions · Date and time functions · Array, struct, and map functions · Aggregate functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result

Here is an example that shows how to compare each employee's salary with the average salary in his or her department:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

+-----------+-------+--------+-------------------+
| depname   | empno | salary | avg               |
+-----------+-------+--------+-------------------+
| personnel | 2     | 3900   | 3700.0            |
| personnel | 5     | 3500   | 3700.0            |
| develop   | 8     | 6000   | 5020.0            |
| develop   | 10    | 5200   | 5020.0            |
| develop   | 11    | 5200   | 5020.0            |
| develop   | 9     | 4500   | 5020.0            |
| develop   | 7     | 4200   | 5020.0            |
| sales     | 1     | 5000   | 4866.666666666667 |
| sales     | 4     | 4800   | 4866.666666666667 |
| sales     | 3     | 4800   | 4866.666666666667 |
+-----------+-------+--------+-------------------+

A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate. The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The previous example showed how to count the average of a column per partition.

You can also control the order in which rows are processed by window functions using ORDER BY within OVER. (The window ORDER BY does not even have to match the order in which the rows are output.) Here is an example:

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

+-----------+-------+--------+--------+
| depname   | empno | salary | rank   |
+-----------+-------+--------+--------+
| personnel | 2     | 3900   | 1      |
| develop   | 8     | 6000   | 1      |
| develop   | 10    | 5200   | 2      |
| develop   | 11    | 5200   | 2      |
| develop   | 9     | 4500   | 4      |
| develop   | 7     | 4200   | 5      |
| sales     | 1     | 5000   | 1      |
| sales     | 4     | 4800   | 2      |
| personnel | 5     | 3500   | 2      |
| sales     | 3     | 4800   | 2      |
+-----------+-------+--------+--------+

There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. Here is an example of using window frames in queries:

SELECT depname, empno, salary,
    avg(salary) OVER(ORDER BY salary ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg,
    min(salary) OVER(ORDER BY empno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_min
FROM empsalary
ORDER BY empno ASC;

+-----------+-------+--------+--------------------+---------+
| depname   | empno | salary | avg                | cum_min |
+-----------+-------+--------+--------------------+---------+
| sales     | 1     | 5000   | 5000.0             | 5000    |
| personnel | 2     | 3900   | 3866.6666666666665 | 3900    |
| sales     | 3     | 4800   | 4700.0             | 3900    |
| sales     | 4     | 4800   | 4866.666666666667  | 3900    |
| personnel | 5     | 3500   | 3700.0             | 3500    |
| develop   | 7     | 4200   | 4200.0             | 3500    |
| develop   | 8     | 6000   | 5600.0             | 3500    |
| develop   | 9     | 4500   | 4500.0             | 3500    |
| develop   | 10    | 5200   | 5133.333333333333  | 3500    |
| develop   | 11    | 5200   | 5466.666666666667  | 3500    |
+-----------+-------+--------+--------------------+---------+

When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for several functions. Instead, each windowing behavior can be named in a WINDOW clause and then referenced in OVER. For example:

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Syntax

The syntax for the OVER-clause is

function([expr])
  OVER(
    [PARTITION BY expr[, …]]
    [ORDER BY expr [ ASC | DESC ][, …]]
    [ frame_clause ]
    )

where frame_clause is one of:

  { RANGE | ROWS | GROUPS } frame_start
  { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end

and frame_start and frame_end can be one of

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

For ROWS and GROUPS, offset is a non-negative integer. For RANGE, the offset must be compatible with the ordering expression; timestamp ordering can use an interval offset, such as RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW.

GROUPS requires ORDER BY. A RANGE frame with an offset requires exactly one ordering expression. When both bounds are UNBOUNDED or CURRENT ROW, RANGE also permits no ordering expression or multiple ordering expressions.

Filter clause for aggregate window functions

Aggregate window functions support the SQL FILTER (WHERE ...) clause to include only rows that satisfy the predicate from the window frame in the aggregation.

sum(salary) FILTER (WHERE salary > 0)
  OVER (PARTITION BY depname ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

If no rows in the frame satisfy the filter for a given output row, COUNT yields 0 while SUM/AVG/MIN/MAX yield NULL.

Aggregate functions

All aggregate functions can be used as window functions.

Null treatment

FIRST_VALUE, LAST_VALUE, NTH_VALUE, LAG, and LEAD accept IGNORE NULLS or RESPECT NULLS between the function call and OVER. The default is RESPECT NULLS: null values participate in choosing a row. IGNORE NULLS skips null values when locating the requested value or offset; it does not remove rows from the query result.

WITH readings(position, value) AS (VALUES (1, 10), (2, NULL), (3, 30))
SELECT position,
  last_value(value) IGNORE NULLS OVER (
    ORDER BY position ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS latest_non_null
FROM readings
ORDER BY position;

The latest_non_null values are 10, 10, and 30. With RESPECT NULLS, they are 10, NULL, and 30. An all-null frame still produces null. The frame remains important: IGNORE NULLS does not make LAST_VALUE look beyond its frame.

Ranking Functions

• cume_dist
• dense_rank
• ntile
• percent_rank
• rank
• row_number

cume_dist

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

cume_dist()

Example

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

Returns the rank of the current row without gaps. This function ranks rows in a dense manner, meaning consecutive ranks are assigned even for identical values.

dense_rank()

Example

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

+-------------+--------+------------+
| department  | salary | dense_rank |
+-------------+--------+------------+
| Sales       | 70000  | 1          |
| Sales       | 50000  | 2          |
| Sales       | 50000  | 2          |
| Sales       | 30000  | 3          |
| Engineering | 90000  | 1          |
| Engineering | 80000  | 2          |
+-------------+--------+------------+

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

Example

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

Returns the percentage rank of the current row within its partition. The value ranges from 0 to 1 and is computed as (rank - 1) / (total_rows - 1).

percent_rank()

Example

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

+-------------+--------+---------------+
| employee_id | salary | percent_rank  |
+-------------+--------+---------------+
| 1           | 30000  | 0.00          |
| 2           | 50000  | 0.50          |
| 3           | 70000  | 1.00          |
+-------------+--------+---------------+

rank

Returns the rank of the current row within its partition, allowing gaps between ranks. This function provides a ranking similar to row_number, but skips ranks for identical values.

rank()

Example

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

+-------------+--------+------+
| department  | salary | rank |
+-------------+--------+------+
| Sales       | 70000  | 1    |
| Sales       | 50000  | 2    |
| Sales       | 50000  | 2    |
| Sales       | 30000  | 4    |
| Engineering | 90000  | 1    |
| Engineering | 80000  | 2    |
+-------------+--------+------+

row_number

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

row_number()

Example

-- 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       |
+-------------+--------+---------+

Analytical Functions

• first_value
• lag
• last_value
• lead
• nth_value

first_value

Returns value evaluated at the row that is the first row of the window frame.

first_value(expression)

Arguments

• expression: Expression to operate on

Example

-- Example usage of the first_value window function:
SELECT department,
  employee_id,
  salary,
  first_value(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary
FROM employees;

+-------------+-------------+--------+------------+
| department  | employee_id | salary | top_salary |
+-------------+-------------+--------+------------+
| Sales       | 1           | 70000  | 70000      |
| Sales       | 2           | 50000  | 70000      |
| Sales       | 3           | 30000  | 70000      |
| Engineering | 4           | 90000  | 90000      |
| Engineering | 5           | 80000  | 90000      |
+-------------+-------------+--------+------------+

lag

Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value).

lag(expression, offset, default)

Arguments

• expression: Expression to operate on
• offset: Integer. Specifies how many rows back the value of expression should be retrieved. Defaults to 1.
• default: The default value if the offset is not within the partition. Must be of the same type as expression.

Example

-- Example usage of the lag window function:
SELECT employee_id,
    salary,
    lag(salary, 1, 0) OVER (ORDER BY employee_id) AS prev_salary
FROM employees;

+-------------+--------+-------------+
| employee_id | salary | prev_salary |
+-------------+--------+-------------+
| 1           | 30000  | 0           |
| 2           | 50000  | 30000       |
| 3           | 70000  | 50000       |
| 4           | 60000  | 70000       |
+-------------+--------+-------------+

last_value

Returns value evaluated at the row that is the last row of the window frame.

last_value(expression)

Arguments

• expression: Expression to operate on

Example

-- SQL example of last_value:
SELECT department,
       employee_id,
       salary,
       last_value(salary) OVER (PARTITION BY department ORDER BY salary) AS running_last_salary
FROM employees;

+-------------+-------------+--------+---------------------+
| department  | employee_id | salary | running_last_salary |
+-------------+-------------+--------+---------------------+
| Sales       | 1           | 30000  | 30000               |
| Sales       | 2           | 50000  | 50000               |
| Sales       | 3           | 70000  | 70000               |
| Engineering | 4           | 40000  | 40000               |
| Engineering | 5           | 60000  | 60000               |
+-------------+-------------+--------+---------------------+

lead

Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value).

lead(expression, offset, default)

Arguments

• expression: Expression to operate on
• offset: Integer. Specifies how many rows forward the value of expression should be retrieved. Defaults to 1.
• default: The default value if the offset is not within the partition. Must be of the same type as expression.

Example

-- Example usage of lead window function:
SELECT
    employee_id,
    department,
    salary,
    lead(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;

+-------------+-------------+--------+--------------+
| employee_id | department  | salary | next_salary  |
+-------------+-------------+--------+--------------+
| 1           | Sales       | 30000  | 50000        |
| 2           | Sales       | 50000  | 70000        |
| 3           | Sales       | 70000  | 0            |
| 4           | Engineering | 40000  | 60000        |
| 5           | Engineering | 60000  | 0            |
+-------------+-------------+--------+--------------+

nth_value

Returns the value evaluated at the nth row of the window frame (counting from 1). Returns NULL if no such row exists.

nth_value(expression, n)

Arguments

• expression: The column from which to retrieve the nth value.
• n: A signed integer literal. Positive positions count from the start of the frame (starting at 1); negative positions count backward from the end (-1 means the last value). Zero or an out-of-range position produces null. With IGNORE NULLS, positions count non-null values.

Example

WITH employees(id, salary) AS (
  VALUES (1, 30000), (2, 40000), (3, 50000), (4, 60000), (5, 70000)
)
SELECT nth_value(salary, 2) OVER (
  ORDER BY salary
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS nth_value
FROM employees
ORDER BY salary;

+-----------+
| nth_value |
+-----------+
| NULL      |
| 40000     |
| 40000     |
| 40000     |
| 40000     |
+-----------+

Attribution

Adapted from the function documentation in Telemetry's vendored Apache DataFusion fork, with Telemetry-specific additions and corrections. Copyright 2019–2026 The Apache Software Foundation. Distributed under the Apache License 2.0; see the Apache notice.