Window Functions

Window functions perform calculations across a set of rows that are related to the current row. Unlike aggregate functions with GROUP BY, window functions do not collapse rows into a single output row. Every input row produces a corresponding output row, with the window function result appended.

Turso supports aggregate functions used as window functions with the default frame definition, the row_number() ranking function, and the FILTER (WHERE ...) clause. The remaining dedicated window functions (rank, dense_rank, ntile, lag, lead, first_value, last_value, nth_value) and custom frame specifications (ROWS, RANGE, or GROUPS with explicit bounds) are not yet supported.

Syntax

aggregate_function(expression) OVER (
    [PARTITION BY expression [, ...]]
    [ORDER BY expression [ASC | DESC] [, ...]]
)

Clause	Description
`aggregate_function`	Any supported aggregate function: `count`, `sum`, `avg`, `min`, `max`, `total`, `group_concat`
`OVER (...)`	Defines the window over which the function operates
`PARTITION BY`	Divides the result set into partitions. The function is applied independently within each partition. If omitted, the entire result set is one partition
`ORDER BY`	Defines the order of rows within each partition. This determines which rows are included in the frame for each calculation

Default Frame

When ORDER BY is specified, the default frame is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means the function considers all rows from the start of the partition up to and including the current row (and any rows with equal ORDER BY values, since the frame mode is RANGE). When ORDER BY is omitted, the default frame covers the entire partition.

Supported Aggregate Functions as Window Functions

Any aggregate function can be used as a window function by adding an OVER clause.

Function	Description
`count(*)`	Number of rows in the frame
`count(expression)`	Number of non-NULL values in the frame
`sum(expression)`	Sum of non-NULL values in the frame
`avg(expression)`	Average of non-NULL values in the frame
`min(expression)`	Minimum value in the frame
`max(expression)`	Maximum value in the frame
`total(expression)`	Sum as REAL (returns 0.0 for empty frames instead of NULL)
`group_concat(expression, separator)`	Concatenation of values in the frame

Ranking Functions

row_number()

Assigns a sequential integer (starting at 1) to each row within its partition, in the order defined by the window’s ORDER BY. This is the one dedicated ranking function supported in Turso.

SELECT
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

department	name	salary	rank_in_dept
Engineering	Alice	90000	1
Engineering	Bob	85000	2
Engineering	Carol	75000	3
Marketing	Dave	70000	1
Marketing	Eve	60000	2

FILTER (WHERE …)

A FILTER (WHERE ...) clause restricts which rows an aggregate window function includes, without affecting the rows returned by the query.

SELECT
    department,
    name,
    salary,
    SUM(salary) FILTER (WHERE salary > 70000) OVER (PARTITION BY department) AS high_earner_total
FROM employees;

PARTITION BY

PARTITION BY divides the rows into groups. The window function resets and recalculates independently for each partition.

SELECT
    department,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

department	name	salary	dept_total
Engineering	Alice	90000	250000
Engineering	Bob	85000	250000
Engineering	Carol	75000	250000
Marketing	Dave	70000	130000
Marketing	Eve	60000	130000

Without PARTITION BY, the function treats the entire result set as one partition:

SELECT
    name,
    salary,
    SUM(salary) OVER () AS company_total
FROM employees;

ORDER BY

ORDER BY within the OVER clause determines row ordering within each partition. Combined with the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), this produces running calculations.

SELECT
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

name	salary	running_total
Eve	60000	60000
Dave	70000	130000
Carol	75000	205000
Bob	85000	290000
Alice	90000	380000

PARTITION BY with ORDER BY

Use both clauses together for running calculations within groups:

SELECT
    department,
    name,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary
    ) AS dept_running_total
FROM employees;

department	name	salary	dept_running_total
Engineering	Carol	75000	75000
Engineering	Bob	85000	160000
Engineering	Alice	90000	250000
Marketing	Eve	60000	60000
Marketing	Dave	70000	130000

Named Windows

The WINDOW clause defines a reusable window specification that can be referenced by multiple window functions in the same query. This avoids repeating the same OVER definition.

SELECT
    department,
    name,
    salary,
    SUM(salary) OVER w AS running_total,
    AVG(salary) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary)
ORDER BY department, salary;

Multiple named windows can be defined:

SELECT
    department,
    name,
    salary,
    SUM(salary) OVER dept AS dept_total,
    SUM(salary) OVER company AS company_total
FROM employees
WINDOW
    dept AS (PARTITION BY department),
    company AS ()
ORDER BY department, name;

Examples

Running Total

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Count per Group

-- Show each employee alongside their department headcount
SELECT
    name,
    department,
    COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees
ORDER BY department, name;

Running Average

SELECT
    date,
    temperature,
    AVG(temperature) OVER (ORDER BY date) AS running_avg_temp
FROM weather_readings
ORDER BY date;

Percentage of Total

SELECT
    product_name,
    revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM products
ORDER BY revenue DESC;

Multiple Window Functions in One Query

SELECT
    department,
    name,
    salary,
    MIN(salary) OVER (PARTITION BY department) AS dept_min,
    MAX(salary) OVER (PARTITION BY department) AS dept_max,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Group Concatenation over a Window

SELECT
    department,
    name,
    GROUP_CONCAT(name, ', ') OVER (PARTITION BY department ORDER BY name) AS names_so_far
FROM employees;

Limitations

The following window function features are not yet supported in Turso:

Feature	Status
`rank()`	Not supported
`dense_rank()`	Not supported
`ntile(N)`	Not supported
`lag(expr, offset, default)`	Not supported
`lead(expr, offset, default)`	Not supported
`first_value(expr)`	Not supported
`last_value(expr)`	Not supported
`nth_value(expr, N)`	Not supported
`cume_dist()`	Not supported
`percent_rank()`	Not supported
Custom frame: `ROWS BETWEEN ...`	Not supported
Custom frame: `RANGE BETWEEN ... AND ...`	Not supported
Custom frame: `GROUPS BETWEEN ...`	Not supported
`EXCLUDE` clause	Not supported

row_number() and FILTER (WHERE ...) are supported — see the sections above.

​Window Functions

​Syntax

​Default Frame

​Supported Aggregate Functions as Window Functions

​Ranking Functions

​row_number()

​FILTER (WHERE …)

​PARTITION BY

​ORDER BY

​PARTITION BY with ORDER BY

​Named Windows

​Examples

​Running Total

​Count per Group

​Running Average

​Percentage of Total

​Multiple Window Functions in One Query

​Group Concatenation over a Window

​Limitations

​See Also

Window Functions

Syntax

Default Frame

Supported Aggregate Functions as Window Functions

Ranking Functions

row_number()

FILTER (WHERE …)

PARTITION BY

ORDER BY

PARTITION BY with ORDER BY

Named Windows

Examples

Running Total

Count per Group

Running Average

Percentage of Total

Multiple Window Functions in One Query

Group Concatenation over a Window

Limitations

See Also