Skip to main content
Window functions perform calculations across a set of rows related to the current row. Unlike aggregate functions with GROUP BY, window functions do not collapse rows — every input row produces an output row with the window function result appended.
Turso currently supports aggregate functions used as window functions with the default frame definition only. Dedicated window functions (row_number, rank, dense_rank, ntile, lag, lead, first_value, last_value, nth_value, cume_dist, percent_rank) and custom frame specifications are not yet supported. See the Limitations table.

​
Syntax

aggregate_function(expression) OVER (
    [PARTITION BY expression [, ...]]
    [ORDER BY expression [ASC | DESC] [, ...]]
)
ClauseDescription
aggregate_functionAny supported aggregate: count, sum, avg, min, max, total, group_concat
OVER (...)Defines the window over which the function operates
PARTITION BYDivides the result set into partitions; the function resets for each partition. If omitted, the entire result set is one partition
ORDER BYOrders rows within each partition. Determines which rows fall within the default frame

​
Default Frame

When ORDER BY is specified inside OVER, the default frame is: 
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This includes all rows from the start of the partition through the current row and any rows with equal ORDER BY values (RANGE semantics). When ORDER BY is omitted, the frame covers the entire partition.

​
Supported Functions

Any standard aggregate function can be used as a window function by adding an OVER clause:
FunctionDescription
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

​
PARTITION BY

PARTITION BY divides rows into groups. The window function resets and recalculates independently within each partition. 
SELECT
    department,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
departmentnamesalarydept_total
EngineeringAlice90000250000
EngineeringBob85000250000
EngineeringCarol75000250000
MarketingDave70000130000
MarketingEve60000130000
Without PARTITION BY, the entire result set is one partition: 
SELECT name, salary, SUM(salary) OVER () AS company_total
FROM employees;

​
ORDER BY Inside OVER

ORDER BY inside the OVER clause determines row ordering within partitions. Combined with the default frame, this produces running calculations: 
SELECT
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;
namesalaryrunning_total
Eve6000060000
Dave70000130000
Carol75000205000
Bob85000290000
Alice90000380000

​
PARTITION BY with ORDER BY

SELECT
    department,
    name,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary
    ) AS dept_running_total
FROM employees;
departmentnamesalarydept_running_total
EngineeringCarol7500075000
EngineeringBob85000160000
EngineeringAlice90000250000
MarketingEve6000060000
MarketingDave70000130000

​
Named Windows

The WINDOW clause defines a reusable window specification to avoid 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: 
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;

​
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:
FeatureStatus
row_number()Not supported
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 expr AND exprNot supported
Custom frame: GROUPS BETWEEN ...Not supported
EXCLUDE clauseNot supported
FILTER (WHERE ...) on window functionsNot supported

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us