Window functions

A window function computes an aggregate or rank over a set of rows related to the current row, returning a value for each row of the input rather than collapsing rows into groups. Where SELECT customer_id, SUM(amount) FROM order GROUP BY customer_id returns one row per customer, SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) FROM order returns one row per order, with each row carrying its customer’s running total. Window functions support analytical queries — running totals, rank within group, period-over-period change, percentile ranking — that are awkward or impossible to express with GROUP BY alone.

SQLite added window functions in version 3.25 (2018), implementing the SQL standard’s surface: OVER, PARTITION BY, ORDER BY within the window, frame specifications (ROWS / RANGE / GROUPS), the standard ranking functions (ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE), the analytic offsets (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), and aggregate-as-window for the standard aggregates and any user-defined aggregate.

OVER

A window function is an aggregate or ranking function followed by an OVER clause that defines the window: the set of rows over which the function is computed.

SELECT
    name,
    amount,
    SUM(amount) OVER () AS grand_total
FROM bouquet_order;

The empty OVER () defines the window as every row in the result; SUM(amount) OVER () returns the same value (the grand total) for every row. The query is otherwise equivalent to a SELECT … FROM … that joins to a scalar subquery, but the window-function form is more concise and the planner produces an efficient execution plan.

PARTITION BY

PARTITION BY divides the input into groups; the window function is computed independently within each group. The form is the analytic counterpart to GROUP BY: each row remains in the result, but the aggregate sees only rows in the same partition.

SELECT
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS lifetime_value
FROM bouquet_order;

Each row carries its customer’s lifetime value. The query is the analytic shape of GROUP BY customer_id — same aggregate, different row-preserving semantics.

ORDER BY (within the window)

An ORDER BY clause inside OVER specifies the order in which rows are processed within a partition. Without ORDER BY, all rows in the partition are visible to the window function simultaneously; with it, rows are processed in the specified order and the function sees the cumulative set up to and including the current row.

SELECT
    customer_id,
    placed_at,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total
FROM bouquet_order
ORDER BY customer_id, placed_at;

Each row’s running_total is the sum of amount from the customer’s first order up to and including the current order; the column traces the customer’s lifetime spend over time. This is the canonical running total report.

The ORDER BY inside OVER is independent of the ORDER BY of the outer query; the inner one defines the window order, the outer one defines the result order.

Frame specifications

A frame is a sub-window: rows within the current partition that are visible to the window function for the current row. The default frame depends on whether ORDER BY is present:

| With ORDER BY | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | | Without ORDER BY | ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |

The first default is cumulative-up-to-and-including-current, which is what produces a running total. The second default is all rows in the partition, which is what SUM(amount) OVER (PARTITION BY customer_id) computes when no order is specified.

The frame is specified explicitly with ROWS BETWEEN … AND … or RANGE BETWEEN … AND …:

-- Trailing seven-day moving average
SELECT
    placed_at,
    AVG(amount) OVER (
        ORDER BY placed_at
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM bouquet_order
ORDER BY placed_at;

The frame bounds:

Bound	Meaning
UNBOUNDED PRECEDING	All rows from the start of the partition.
n PRECEDING	Up to n rows before the current row.
CURRENT ROW	The current row.
n FOLLOWING	Up to n rows after the current row.
UNBOUNDED FOLLOWING	All rows to the end of the partition.

The frame unit is ROWS (count of physical rows) or RANGE (logical range based on the value of the ORDER BY column) or GROUPS (count of distinct values in the ORDER BY column).

ROWS is the most-used; the count is exact. RANGE is needed when the order column has duplicates and the frame should cover all rows with the same value. GROUPS is rare in practice.

-- Three rows centred on current
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- Last 30 days (RANGE on a date column)
ORDER BY placed_at RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW

(The INTERVAL syntax is illustrative; SQLite’s syntax for date-based RANGE is more verbose because it does not have a native interval type.)

Ranking functions

The standard ranking functions return a value derived from the row’s position within the partition.

Function	Returns
ROW_NUMBER()	The row’s 1-based position within the partition.
RANK()	The row’s rank, with ties sharing a rank and gaps after ties.
DENSE_RANK()	The row’s rank, with ties sharing a rank and no gaps.
PERCENT_RANK()	The row’s percentile rank, in [0, 1].
CUME_DIST()	The cumulative distribution: fraction of rows with a value ≤ the current.
NTILE(n)	The row’s tile (1 to n) of an n-way equal split.

SELECT
    customer_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rk_unique,
    RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rk_with_gaps,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rk_dense
FROM bouquet_order;

The conventional uses:

• ROW_NUMBER() for top-N per group: select the rows with rk_unique <= 3.
• RANK() and DENSE_RANK() for leaderboards where ties are meaningful.
• NTILE(4) for splitting a population into quartiles.

Top-N per group

The canonical use of ROW_NUMBER() is selecting the top n rows per group:

WITH ranked AS (
    SELECT
        customer_id,
        amount,
        placed_at,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rk
    FROM bouquet_order
)
SELECT customer_id, amount, placed_at
FROM ranked
WHERE rk <= 3
ORDER BY customer_id, rk;

The query returns each customer’s three largest orders. The pattern generalises: replace <= 3 with any predicate that selects rows by their position within the partition.

Analytic offsets

The analytic-offset functions return a value from a different row in the partition.

Function	Returns
LAG(col, n, default)	col from n rows before; default if absent.
LEAD(col, n, default)	col from n rows after; default if absent.
FIRST_VALUE(col)	col from the first row of the frame.
LAST_VALUE(col)	col from the last row of the frame.
NTH_VALUE(col, n)	col from the nth row of the frame.

The default offset is 1 row; the default for absent values is NULL.

-- Period-over-period change
SELECT
    placed_at,
    amount,
    amount - LAG(amount) OVER (ORDER BY placed_at) AS change_from_previous,
    amount / LAG(amount) OVER (ORDER BY placed_at) - 1 AS pct_change
FROM bouquet_order;

The conventional use is period-over-period analysis: compare each row’s value to the previous, computing differences or percentage changes.

FIRST_VALUE and LAST_VALUE are sensitive to the frame: with the default RANGE frame, LAST_VALUE returns the current row’s value (because the frame ends at CURRENT ROW); the conventional remedy is to specify an unbounded frame:

SELECT
    customer_id,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY placed_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order_amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY placed_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM bouquet_order;

Aggregate-as-window

Any aggregate function — SUM, COUNT, AVG, MIN, MAX, GROUP_CONCAT — can be used as a window function by adding OVER:

SELECT
    customer_id,
    amount,
    AVG(amount) OVER (PARTITION BY customer_id) AS customer_avg,
    amount - AVG(amount) OVER (PARTITION BY customer_id) AS deviation,
    COUNT(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM bouquet_order;

User-defined aggregates registered through the C API can also be used as window functions, provided they implement the inverse-aggregation step (xInverse) required for sliding windows.

WINDOW clause

A named window can be declared once and referenced by name; this saves repetition when several columns share the same window:

SELECT
    customer_id,
    amount,
    SUM(amount) OVER win AS running_total,
    AVG(amount) OVER win AS running_avg,
    COUNT(*)    OVER win AS order_number
FROM bouquet_order
WINDOW win AS (PARTITION BY customer_id ORDER BY placed_at)
ORDER BY customer_id, placed_at;

The WINDOW clause is positioned after HAVING and before ORDER BY. Several named windows may be declared, separated by commas. A named window can be used in the inline form OVER win or extended in the inline form OVER (win ROWS BETWEEN …).

Practical patterns

Running total

SELECT placed_at, amount,
       SUM(amount) OVER (ORDER BY placed_at) AS running_total
FROM bouquet_order ORDER BY placed_at;

Moving average

SELECT placed_at, amount,
       AVG(amount) OVER (ORDER BY placed_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM bouquet_order ORDER BY placed_at;

Top-N per group

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rk
    FROM bouquet_order
)
SELECT * FROM ranked WHERE rk <= 3;

Percent of total

SELECT customer_id, amount,
       amount * 1.0 / SUM(amount) OVER () AS share_of_total
FROM bouquet_order;

Period-over-period

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
       (revenue - LAG(revenue) OVER (ORDER BY month)) * 1.0
           / LAG(revenue) OVER (ORDER BY month) AS mom_pct_change
FROM monthly_revenue;

Gaps and islands

A classic use: identifying contiguous runs of dates. The technique is to subtract ROW_NUMBER() from a date sequence; consecutive dates produce identical results, which can then be grouped:

WITH numbered AS (
    SELECT placed_at, ROW_NUMBER() OVER (ORDER BY placed_at) AS rn
    FROM (SELECT DISTINCT date(placed_at) AS placed_at FROM bouquet_order)
),
grouped AS (
    SELECT placed_at, date(placed_at, '-' || rn || ' days') AS island_key
    FROM numbered
)
SELECT MIN(placed_at) AS island_start, MAX(placed_at) AS island_end
FROM grouped GROUP BY island_key
ORDER BY island_start;

The technique is widely used in time-series analysis where contiguous-period detection is needed.

Performance

Window functions are evaluated by sorting the input within each partition and computing the aggregate as the sort proceeds. The cost is dominated by the sort. An index on the partition-and-order columns eliminates the sort:

CREATE INDEX ix_order_partition ON bouquet_order (customer_id, placed_at);

-- Plan: window evaluated using the index, no temp B-tree
SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY placed_at)
FROM bouquet_order;

EXPLAIN QUERY PLAN reports USE TEMP B-TREE FOR window when a sort is required and elides the line when an index serves. For a query that scans a substantial fraction of the table, the index can be the difference between an interactive response and a noticeable wait.