Window Functions Overview

Window functions perform calculations across sets of table rows related to the current row. Unlike aggregate functions that return a single result for a group of rows, window functions return a value for every row while considering a "window" of related rows defined by the OVER clause.

Syntax

function_name(arguments) OVER (
    [PARTITION BY column [, ...]]
    [ORDER BY column [ASC | DESC] [, ...]]
    [frame_clause]
)

• PARTITION BY: Divides rows into groups; the function resets for each group
• ORDER BY: Defines the order of rows within each partition
• frame_clause: Specifies which rows relative to the current row to include (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

Some functions (first_value, last_value, lag, lead) also support IGNORE NULLS or RESPECT NULLS before the OVER keyword to control null handling.

When multiple window functions share the same definition, use the WINDOW clause to define it once:

SELECT avg(price) OVER w, sum(amount) OVER w
FROM trades
WINDOW w AS (PARTITION BY symbol ORDER BY timestamp)

For complete syntax details including frame specifications, exclusion options, and named windows, see OVER Clause Syntax.

:::info Window function arithmetic (9.3.1+) Arithmetic operations on window functions (e.g., sum(...) OVER (...) / sum(...) OVER (...)) are supported from version 9.3.1. Earlier versions require wrapping window functions in CTEs or subqueries. :::

Quick reference

Respects Frame: Functions marked "Yes" use the frame clause (ROWS/RANGE BETWEEN). Functions marked "No" operate on the entire partition regardless of frame specification.

When to use window functions

Window functions are essential for analytics tasks where you need to:

• Calculate running totals or cumulative sums
• Compute moving averages over time periods
• Find the maximum or minimum value within a sequence
• Rank items within categories
• Access previous or next row values without self-joins
• Compare each row to an aggregate of related rows

Example: Moving average

SELECT
    symbol,
    price,
    timestamp,
    avg(price) OVER (
        PARTITION BY symbol
        ORDER BY timestamp
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM trades
WHERE timestamp IN '[$today]'
LIMIT 100;

This calculates a moving average over the current row plus three preceding rows, grouped by symbol.

How window functions work

A window function has three key components:

function_name(arguments) OVER (
    [PARTITION BY column]      -- Divide into groups
    [ORDER BY column]          -- Order within groups
    [frame_specification]      -- Define which rows to include
)

1. Partitioning

PARTITION BY divides rows into independent groups. The window function resets for each partition—calculations start fresh, as if each group were a separate table.

When to use it: When storing multiple instruments in the same table, you typically want calculations isolated per symbol. For example:

• Cumulative volume per symbol (not across all instruments)
• Moving average price per symbol (not mixing BTC-USDT with ETH-USDT)
• Intraday high/low per symbol

-- Without PARTITION BY: cumulative volume across ALL symbols (mixing instruments)
sum(amount) OVER (ORDER BY timestamp)

-- With PARTITION BY: cumulative volume resets for each symbol
sum(amount) OVER (PARTITION BY symbol ORDER BY timestamp)

Without PARTITION BY, all rows are treated as a single partition.

2. Ordering

ORDER BY within the OVER clause determines the logical order for calculations:

-- Row numbers ordered by timestamp
row_number() OVER (ORDER BY timestamp)

This is independent of the query-level ORDER BY.

:::tip Time-series optimization For tables with a designated timestamp, data is already ordered by time. When your window ORDER BY matches the designated timestamp, QuestDB skips redundant sorting—no performance penalty. :::

3. Frame specification

The frame defines which rows relative to the current row are included in the calculation:

-- Sum of current row plus 2 preceding rows
sum(price) OVER (
    ORDER BY timestamp
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

For complete frame syntax details, see OVER Clause Syntax.

Aggregate vs window functions

The key difference: aggregate functions collapse rows into one result, while window functions keep all rows and add a computed column.

Source data:

Aggregate function — returns one row:

SELECT symbol, avg(price) AS avg_price
FROM trades
GROUP BY symbol;

Window function — returns all rows with computed column:

SELECT timestamp, symbol, price,
       avg(price) OVER (PARTITION BY symbol) AS avg_price
FROM trades;

Each row keeps its original data plus the average—useful for comparing each price to the mean, calculating deviations, or adding running totals alongside the raw values.

ROWS vs RANGE frames

QuestDB supports two frame types:

ROWS frame

Based on physical row count:

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

Includes exactly 4 rows: current row plus 3 before it.

RANGE frame

Based on values in the ORDER BY column (must be a timestamp):

RANGE BETWEEN '1' MINUTE PRECEDING AND CURRENT ROW

Includes all rows within 1 minute of the current row's timestamp.

:::note RANGE frames have a known limitation: rows with the same ORDER BY value ("peers") do not produce identical results as required by the SQL standard. QuestDB currently processes peers as distinct rows rather than treating them as a group. See GitHub issue #5177 for details. :::

For complete frame syntax, see OVER Clause Syntax.

Common patterns

Running total

Use the CUMULATIVE shorthand for running totals:

SELECT
    timestamp,
    amount,
    sum(amount) OVER (
        ORDER BY timestamp
        CUMULATIVE
    ) AS running_total
FROM trades
WHERE timestamp IN '[$today]';

This is equivalent to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

VWAP (Volume-Weighted Average Price)

For high-frequency market data, VWAP is typically calculated over OHLC time series using the typical price (high + low + close) / 3:

DECLARE @symbol := 'BTC-USDT'

WITH ohlc AS (
    SELECT
        timestamp AS ts,
        symbol,
        first(price) AS open,
        max(price) AS high,
        min(price) AS low,
        last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE timestamp IN '2024-05-22' AND symbol = @symbol
    SAMPLE BY 1m
)
SELECT
    ts,
    symbol,
    open, high, low, close, volume,
    sum((high + low + close) / 3 * volume) OVER w / sum(volume) OVER w AS vwap
FROM ohlc
WINDOW w AS (ORDER BY ts CUMULATIVE)
ORDER BY ts;

Compare to group average

SELECT
    symbol,
    price,
    timestamp,
    avg(price) OVER w AS symbol_avg,
    price - avg(price) OVER w AS diff_from_avg
FROM trades
WHERE timestamp IN '[$today]'
WINDOW w AS (PARTITION BY symbol);

Rank within category

SELECT
    symbol,
    price,
    timestamp,
    rank() OVER (
        PARTITION BY symbol
        ORDER BY price DESC
    ) AS price_rank
FROM trades
WHERE timestamp IN '[$today]';

Access previous row

SELECT
    timestamp,
    price,
    lag(price) OVER w AS prev_price,
    price - lag(price) OVER w AS price_change
FROM trades
WHERE timestamp IN '[$today]' AND symbol = 'BTC-USDT'
WINDOW w AS (ORDER BY timestamp);

Next steps

• Function Reference: Detailed documentation for each window function
• OVER Clause Syntax: Complete syntax for partitioning, ordering, and frame specifications

:::tip Looking for WINDOW JOIN? WINDOW JOIN is a separate feature for aggregating data from a different table within a time window. Use window functions (this page) for calculations within a single table; use WINDOW JOIN to correlate two time-series tables. :::

Common mistakes

Using window functions in WHERE

Window functions cannot be used directly in WHERE clauses:

SELECT symbol, price
FROM trades
WHERE avg(price) OVER (ORDER BY timestamp) > 100;

Use a CTE or subquery instead:

WITH prices AS (
    SELECT
        symbol,
        price,
        avg(price) OVER (ORDER BY timestamp) AS moving_avg
    FROM trades
    WHERE timestamp IN '[$today]'
)
SELECT * FROM prices
WHERE moving_avg > 100;

Missing ORDER BY

Without ORDER BY, the window includes all rows in the partition, which may not be the intended behavior:

SELECT
    symbol,
    price,
    timestamp,
    avg(price) OVER (PARTITION BY symbol) AS avg_price  -- Same value for all rows in partition
FROM trades
WHERE timestamp IN '[$today]';

Add ORDER BY for cumulative/moving calculations:

SELECT
    symbol,
    price,
    timestamp,
    avg(price) OVER (
        PARTITION BY symbol
        ORDER BY timestamp
    ) AS running_avg
FROM trades
WHERE timestamp IN '[$today]';