Window Functions

A window function computes a value for each row across a set of related rows, called a window, without collapsing those rows into a single group the way GROUP BY does. The window is defined by the OVER clause, so aggregate values, rankings and neighbors can appear alongside the original rows in the same result set.

OVER and PARTITION BY

PARTITION BY splits the rows into independent groups, and the function is evaluated separately within each group:

SELECT region, salesperson, amount,
       sum(amount) OVER (PARTITION BY region) AS region_total,
       round(100.0 * amount /
             sum(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales;

Every row keeps its identity, yet it now also carries the regional total and its percentage share of it, something a plain GROUP BY cannot express in one step.

Ranking Functions

Ranking functions require an ORDER BY inside the window definition. ROW_NUMBER() is unique, RANK() leaves gaps after ties and DENSE_RANK() does not:

SELECT name, dept, salary,
       row_number() OVER (ORDER BY salary DESC) AS rn,
       rank()       OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank,
       ntile(4)     OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

The result looks like a small leaderboard stored side by side: rn counts rows overall, dept_rank restarts inside each department and salary_quartile bins people into four equally sized buckets. A common pattern takes the top n per group by wrapping the ranked query in a filtered query with dept_rank from 1 through n.

LAG, LEAD and Offset Functions

LAG() and LEAD() reach to previous and following rows, which makes period-over-period comparisons straightforward:

SELECT month, revenue,
       lag(revenue)  OVER (ORDER BY month) AS prev_month,
       revenue - lag(revenue) OVER (ORDER BY month) AS change,
       round(avg(revenue) OVER (
             ORDER BY month ROWS BETWEEN 2 PRECEDING
                              AND CURRENT ROW), 2) AS moving_avg
FROM monthly_revenue;

Both functions accept an offset and a default, for example lag(revenue, 1, 0) returns 0 when there is no previous month.

Window Frames

With an ORDER BY present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Explicit frames control exactly which rows contribute: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW uses physical row offsets for rolling averages, while ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING spans the whole partition regardless of ordering.

Named Windows

Repeated window definitions can be declared once and referenced by name with the WINDOW clause:

SELECT region, salesperson, amount,
       sum(amount)       OVER w AS total,
       round(avg(amount) OVER w, 2) AS avg
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY amount);

Summary

Window functions in DuckDB follow the standard SQL model: OVER with PARTITION BY and ORDER BY defines the scope, ranking functions build leaderboards, LAG() and LEAD() compare neighboring rows, frames control rolling computations, and the WINDOW clause removes repetition. Because windows never collapse rows, they are the natural tool for shares, ranks, moving averages and per-group top-n queries.