Window Function Syntax - Questdb

The OVER clause defines the window for a window function. The WINDOW clause lets you define reusable named windows and build on them with inheritance. This page covers the complete syntax for both. For an introduction to window functions, see the Overview.

Syntax

sql

-- Inline window definition
function_name(arguments) [IGNORE NULLS | RESPECT NULLS] OVER (
    [PARTITION BY column [, ...]]
    [ORDER BY column [ASC | DESC] [, ...]]
    [frame_clause]
    [exclusion_clause]
)

-- Named window definition
function_name(arguments) OVER window_name
...
WINDOW window_name AS ([base_window_name] [window_definition]) [, ...]

Where frame_clause is one of:

sql

ROWS | RANGE BETWEEN frame_start AND frame_end
ROWS | RANGE frame_start
CUMULATIVE

And frame_start is one of:

sql

UNBOUNDED PRECEDING
<value> PRECEDING
CURRENT ROW

And frame_end is one of:

sql

CURRENT ROW
<value> FOLLOWING
UNBOUNDED FOLLOWING

For ROWS frames, <value> is an integer (number of rows). For RANGE frames, <value> is a time interval like '1' MINUTE or '5' SECOND.

And exclusion_clause is:

sql

EXCLUDE CURRENT ROW | EXCLUDE NO OTHERS

Component	Description
`IGNORE NULLS`	Skip null values when evaluating the function
`RESPECT NULLS`	Include null values (default behavior)
`PARTITION BY`	Divides the result set into partitions
`ORDER BY`	Orders rows within each partition
`ROWS \| RANGE`	Defines the window frame relative to current row
`CUMULATIVE`	Shorthand for `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
`EXCLUDE`	Optionally excludes rows from the frame

Multiple window functions can share a definition using the WINDOW clause.

PARTITION BY

PARTITION BY divides the result set into groups. The window function operates independently on each partition.

questdb-sql

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

Without PARTITION BY, all rows are treated as a single partition
Multiple columns can be specified: PARTITION BY symbol, exchange

ORDER BY

ORDER BY within the OVER clause determines the logical order for window calculations.

questdb-sql

SELECT
    symbol,
    price,
    timestamp,
    row_number() OVER (
        PARTITION BY symbol
        ORDER BY timestamp
    ) AS seq
FROM trades
WHERE timestamp IN '[$today]'
LIMIT 100;

Important:

This is independent of the query-level ORDER BY
Required for ranking functions (row_number, rank, dense_rank, percent_rank)
Required for RANGE frames
Required for CUMULATIVE
Without ORDER BY, all rows in the partition are peers

:::tip Time-series optimization For tables with a designated timestamp column, data is already ordered by time. When your ORDER BY matches the designated timestamp, QuestDB's optimizer recognizes this and skips redundant sorting. You still need to specify ORDER BY for cumulative calculations, but there's no performance penalty. :::

IGNORE NULLS / RESPECT NULLS

The IGNORE NULLS and RESPECT NULLS clauses control how null values are handled by certain window functions. These clauses appear before the OVER keyword.

Supported functions: first_value(), last_value(), lag(), lead()

RESPECT NULLS (default)

By default, null values are included in the evaluation. This is equivalent to explicitly specifying RESPECT NULLS:

questdb-sql

SELECT
    timestamp,
    price,
    lag(price) OVER (ORDER BY timestamp) AS prev_price
FROM trades;

If the previous row has a null price, prev_price will be null.

IGNORE NULLS

With IGNORE NULLS, the function skips over null values to find the next non-null value:

questdb-sql

SELECT
    timestamp,
    price,
    lag(price) IGNORE NULLS OVER (ORDER BY timestamp) AS prev_non_null_price
FROM trades;

This returns the most recent non-null price value, skipping any intermediate nulls.

Common use cases:

Filling gaps: Use first_value() IGNORE NULLS or last_value() IGNORE NULLS to carry forward/backward the last known value
Sparse data: Use lag() IGNORE NULLS to reference the previous actual measurement, ignoring missing readings

Frame types and behavior

Window frames specify which rows are included in the calculation relative to the current row.

mermaid

sequenceDiagram
    participant R1 as Row at 09:00
    participant R2 as Row at 09:02
    participant R3 as Row at 09:03
    participant R4 as Row at 09:04
(Current Row)

    Note over R4: Calculating at 09:04

    rect rgb(191, 223, 255)
    Note over R2,R4: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    end

    rect rgb(255, 223, 191)
    Note over R3,R4: RANGE BETWEEN
'1' MINUTE PRECEDING
AND CURRENT ROW
    end

ROWS frame

Defines the frame based on a physical number of rows:

questdb-sql

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This includes exactly 3 rows: the current row and two preceding rows.

mermaid

sequenceDiagram
    participant R1 as Row 1
    participant R2 as Row 2
    participant R3 as Row 3
    participant R4 as Row 4
    participant R5 as Row 5

    Note over R1: Frame: Row1
    Note over R2: Frame: Row1, Row2
    Note over R3: Frame: Row1, Row2, Row3
    Note over R4: Frame: Row2, Row3, Row4
    Note over R5: Frame: Row3, Row4, Row5

Example:

questdb-sql

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

RANGE frame

Defines the frame based on values in the ORDER BY column rather than row count.

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

Requirements:

Data must be ordered by the designated timestamp column
The window is calculated based on timestamp values

questdb-sql

RANGE BETWEEN '1' MINUTE PRECEDING AND CURRENT ROW

With a current row at 09:04, this includes all rows with timestamps between 09:03 and 09:04 (inclusive).

mermaid

sequenceDiagram
    participant R1 as Row at 09:00
    participant R2 as Row at 09:02
    participant R3 as Row at 09:03
    participant R4 as Row at 09:04
(Current Row)

    Note over R4: Calculating at 09:04

    rect rgba(255, 223, 191)
    Note over R3,R4: RANGE BETWEEN
'1' MINUTE PRECEDING
AND CURRENT ROW
    end

    Note over R1,R2: Outside 1-minute range

Supported time units:

Unit	Plural
`day`	`days`
`hour`	`hours`
`minute`	`minutes`
`second`	`seconds`
`millisecond`	`milliseconds`
`microsecond`	`microseconds`

Example with multiple intervals:

questdb-sql

SELECT
    timestamp,
    bids[1,1] AS best_bid,
    bids[2,1] AS volume_l1,
    AVG(best_bid) OVER (
        ORDER BY timestamp
        RANGE BETWEEN '5' MINUTE PRECEDING AND CURRENT ROW
    ) AS avg_5min,
    COUNT(*) OVER (
        ORDER BY timestamp
        RANGE BETWEEN '100' MILLISECOND PRECEDING AND CURRENT ROW
    ) AS updates_100ms,
    SUM(volume_l1) OVER (
        ORDER BY timestamp
        RANGE BETWEEN '2' SECOND PRECEDING AND CURRENT ROW
    ) AS volume_2sec
FROM market_data
WHERE timestamp IN '[$today]'
LIMIT 100;

This query demonstrates different time intervals in action:

5-minute moving average of best bid price (in the example table, bids[1] contains bid prices and bids[2] contains bid sizes)
Update frequency in 100ms windows
2-second rolling volume

Note that each window calculation is based on the timestamp values, not the number of rows. The number of rows included can vary depending on how many records exist within each time interval.

CUMULATIVE shorthand

CUMULATIVE is a convenient shorthand for running totals and cumulative calculations. It's equivalent to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Requirements:

ORDER BY clause is required

questdb-sql

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

This is equivalent to:

questdb-sql

SELECT
    symbol,
    price,
    timestamp,
    sum(price) OVER (
        PARTITION BY symbol
        ORDER BY timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM trades
WHERE timestamp IN '[$today]';

VWAP example:

For high-frequency data, VWAP is typically calculated over OHLC using typical price:

questdb-sql

DECLARE @symbol := 'BTC-USDT'

WITH ohlc AS (
    SELECT
        timestamp AS ts,
        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, 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);

Frame shorthand syntax

When you omit BETWEEN ... AND, the frame end defaults to CURRENT ROW:

Shorthand	Equivalent
`ROWS UNBOUNDED PRECEDING`	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
`ROWS 5 PRECEDING`	`ROWS BETWEEN 5 PRECEDING AND CURRENT ROW`
`RANGE '1' MINUTE PRECEDING`	`RANGE BETWEEN '1' MINUTE PRECEDING AND CURRENT ROW`
`CUMULATIVE`	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

Example:

questdb-sql

SELECT
    timestamp,
    price,
    -- These are equivalent:
    sum(price) OVER (ORDER BY timestamp ROWS UNBOUNDED PRECEDING) AS sum1,
    sum(price) OVER (ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum2,
    sum(price) OVER (ORDER BY timestamp CUMULATIVE) AS sum3
FROM trades
WHERE timestamp IN '[$today]';

Frame boundaries

Frame boundaries determine which rows are included:

Boundary	Description
`UNBOUNDED PRECEDING`	First row of the partition
`<value> PRECEDING`	N rows/interval before current row
`CURRENT ROW`	The current row
`<value> FOLLOWING`	N rows/interval after current row (limited support)
`UNBOUNDED FOLLOWING`	Last row of partition (only when start is `UNBOUNDED PRECEDING`)

Default frames

When frame clause is omitted:

Scenario	Default Frame
No `ORDER BY`	Entire partition (all rows are peers)
With `ORDER BY`	`RANGE UNBOUNDED PRECEDING` (start to current row)

Restrictions

Frame start can only be:
- UNBOUNDED PRECEDING
- <value> PRECEDING
- CURRENT ROW
Frame end can only be:
- CURRENT ROW
- <value> FOLLOWING
- UNBOUNDED FOLLOWING (only when start is UNBOUNDED PRECEDING)
RANGE frames must be ordered by a designated timestamp
CUMULATIVE requires an ORDER BY clause

Exclusion options

EXCLUDE NO OTHERS (default)

Includes all rows in the frame:

mermaid

sequenceDiagram
    participant R1 as Row 1
    participant R2 as Row 2
    participant CR as Current Row
    participant R4 as Row 4

    rect rgba(255, 223, 191)
    Note over R1,CR: Frame includes all rows
    end

EXCLUDE CURRENT ROW

Excludes the current row from frame calculations:

mermaid

sequenceDiagram
    participant R1 as Row 1
    participant R2 as Row 2
    participant CR as Current Row
    participant R4 as Row 4

    rect rgba(255, 223, 191)
    Note over R1,R2: Frame includes preceding rows only
    end
    rect rgba(255, 0, 0, 0.1)
    Note over CR: Excluded
    end

When the frame ends at CURRENT ROW, EXCLUDE CURRENT ROW automatically adjusts the end boundary to 1 PRECEDING.

Example:

questdb-sql

SELECT
    timestamp,
    price,
    SUM(price) OVER (
        ORDER BY timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        EXCLUDE CURRENT ROW
    ) AS cumulative_sum_excluding_current
FROM trades
WHERE timestamp IN '[$today]'
LIMIT 100;

Named windows (WINDOW clause)

When multiple window functions share the same window definition, you can define the window once and reference it by name. This reduces repetition and improves readability.

Syntax:

sql

SELECT
    columns,
    window_function() OVER window_name,
    another_function() OVER window_name
FROM table
WINDOW window_name AS (window_definition) [, ...]
ORDER BY column
LIMIT n;

The WINDOW clause appears after WHERE and before ORDER BY.

Example:

questdb-sql

SELECT
    timestamp,
    symbol,
    price,
    avg(price) OVER w AS avg_price,
    min(price) OVER w AS min_price,
    max(price) OVER w AS max_price
FROM trades
WHERE timestamp IN '[$today]' AND symbol = 'BTC-USDT'
WINDOW w AS (ORDER BY timestamp ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
LIMIT 100;

Multiple named windows

You can define multiple windows in a single WINDOW clause:

questdb-sql

SELECT
    timestamp,
    symbol,
    price,
    avg(price) OVER short_window AS avg_10,
    avg(price) OVER long_window AS avg_50
FROM trades
WHERE timestamp IN '[$today]' AND symbol = 'BTC-USDT'
WINDOW
    short_window AS (ORDER BY timestamp ROWS BETWEEN 9 PRECEDING AND CURRENT ROW),
    long_window AS (ORDER BY timestamp ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)
LIMIT 100;

Mixing inline and named windows

You can use both named windows and inline OVER (...) definitions in the same query:

questdb-sql

SELECT
    timestamp,
    symbol,
    price,
    avg(price) OVER w AS moving_avg,
    row_number() OVER (PARTITION BY symbol ORDER BY timestamp) AS seq
FROM trades
WHERE timestamp IN '[$today]' AND symbol = 'BTC-USDT'
WINDOW w AS (ORDER BY timestamp ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
LIMIT 100;

Works with CTEs and subqueries

Named windows work within CTEs and subqueries:

questdb-sql

WITH price_stats AS (
    SELECT
        timestamp,
        symbol,
        price,
        avg(price) OVER w AS moving_avg,
        price - avg(price) OVER w AS deviation
    FROM trades
    WHERE timestamp IN '[$today]' AND symbol = 'BTC-USDT'
    WINDOW w AS (ORDER BY timestamp ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
)
SELECT * FROM price_stats
WHERE deviation > 10
LIMIT 100;

Window inheritance

A named window can reference another named window as its base, inheriting its PARTITION BY, ORDER BY, and frame clauses. The child window can then add or override clauses on top.

questdb-sql

SELECT
    timestamp,
    symbol,
    price,
    avg(price) OVER w1 AS symbol_avg,
    avg(price) OVER w2 AS moving_avg
FROM trades
WHERE timestamp IN '[$today]' AND symbol = 'BTC-USDT'
WINDOW
    w1 AS (ORDER BY timestamp),
    w2 AS (w1 ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
LIMIT 100;

Here w2 inherits ORDER BY timestamp from w1 and adds a frame clause.

Chained inheritance is supported — a window can inherit from a window that itself inherits from another:

questdb-sql

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

Merge rules follow the SQL standard:

Clause	Behavior
`PARTITION BY`	Always inherited from the base. The child cannot specify its own.
`ORDER BY`	Child's takes precedence if specified, otherwise inherited from the base.
Frame	Child's takes precedence if specified, otherwise inherited from the base.

Restrictions:

The base window must be defined earlier in the WINDOW clause (no forward references).
A child window that references a base cannot include its own PARTITION BY.
Circular and self-references are not allowed.

Performance considerations

ROWS frames typically perform better than RANGE frames for large datasets
Partitioning improves performance by processing smaller data chunks
Consider index usage when ordering by timestamp columns
Narrow windows process less data than wide windows

Common pitfalls

Window functions in WHERE

Window functions cannot be used directly in WHERE clauses:

questdb-sql

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

Use a CTE instead:

questdb-sql

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

Missing ORDER BY

Without ORDER BY, the function operates on the entire partition. All rows show the same value:

questdb-sql

-- Potential issue: all rows show the same sum
SELECT
    symbol,
    price,
    timestamp,
    sum(price) OVER () AS total_sum
FROM trades
WHERE timestamp IN '[$today]';

With PARTITION BY but no ORDER BY, all rows within each partition show the same value:

questdb-sql

-- Potential issue: same value for all rows in each partition
SELECT
    symbol,
    price,
    timestamp,
    sum(price) OVER (PARTITION BY symbol) AS symbol_total
FROM trades
WHERE timestamp IN '[$today]';

For cumulative or moving calculations, you need both PARTITION BY and ORDER BY:

questdb-sql

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