Window Functions

An aggregate with GROUP BY collapses rows. A window function computes across related rows while retaining one output row for every input row. This makes windows useful for ranks, running totals, lags, and within-group comparisons.

Prerequisites

Understand grouping grain and CTEs. You should also use deterministic ordering with a unique tie-breaker.

Learning goals

• Separate partitioning, ordering, and the window frame.
• Compute running aggregates at detail-row grain.
• Use ROW_NUMBER for a deterministic top row per group.
• Label DuckDB's QUALIFY convenience separately from portable SQL.

Partition, order, frame

PARTITION BY restarts a calculation for each customer. The window's ORDER BY defines sequence inside that partition. The frame defines which rows around the current row feed frame-sensitive functions such as SUM.

Write frames explicitly. With duplicate sort keys, the default RANGE frame may include peers together, while ROWS counts physical rows. Add a stable tie-breaker whenever “first” or a running sequence must be reproducible.

Worked example

This Standard SQL running total retains order grain. SUM ignores the missing amount on order 104, so Ben's running known total remains 25.00.

SELECT order_id,
       customer_id,
       amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY ordered_at, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM orders
ORDER BY customer_id, ordered_at, order_id;

order_id	customer_id	amount	running_amount
101	1	40.00	40.00
102	1	60.00	100.00
103	2	25.00	25.00
104	2	NULL	25.00
105	3	80.00	80.00

DuckDB-specific: QUALIFY filters after window expressions, avoiding an extra CTE. The window function itself is standard; the portable form calculates ROW_NUMBER in a subquery and filters outside it.

SELECT order_id, customer_id, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY ordered_at DESC, order_id DESC
) = 1
ORDER BY customer_id;

order_id	customer_id	amount
102	1	60.00
104	2	NULL
105	3	80.00

Limitations and common mistakes

• ROW_NUMBER() without a unique order can choose a different tied row on another run.
• LAST_VALUE often surprises because the default frame ends at the current peer group; specify the intended frame.
• A window does not repair earlier join fanout. It operates on the rows produced before the window stage.
• Filtering in WHERE happens before windows; use a subquery/CTE or a documented implementation convenience such as QUALIFY.

Practice

Add LAG(amount) by customer and calculate the difference from the previous known order. Decide how the missing cancelled amount should be represented rather than silently filling it.

Next steps

Next, combine compatible query results vertically with set operations.

Primary sources

Checked 2026-10-14: DuckDB window functions, QUALIFY, and PostgreSQL window-function tutorial.