SQL Window Function Syntax
Compute across a set of related rows while keeping every row in the result.
A window function calculates a value across a set of rows related to the current row, without collapsing them the wayGROUP BY does. Every input row stays in the output, with the computed value added as a new column.
select product_name,
category_id,
price,
row_number() over (
partition by category_id
order by price desc
) as price_rank
from productsReading the OVER clause
The function name says what to calculate; the OVERclause says which rows it can see. That set of rows is the "window." It has three optional parts:
| Part | What it controls |
|---|---|
PARTITION BY | Splits rows into groups; the calculation restarts per group. |
ORDER BY | Orders rows inside each partition, which is needed for ranking and offsets. |
Frame (ROWS/RANGE) | Limits which ordered rows the calculation sees (running totals). |
An empty over () treats the whole result as one window, which is handy for adding a grand total or overall average to every row.
Ranking functions
All three number rows within a partition, but they differ on how they treat ties.
select category_id,
product_name,
price,
row_number() over (partition by category_id order by price desc) as rn,
rank() over (partition by category_id order by price desc) as rnk,
dense_rank() over (partition by category_id order by price desc) as drnk
from products| Function | On ties | Example sequence |
|---|---|---|
row_number() | Always distinct, arbitrary order. | 1, 2, 3, 4 |
rank() | Ties share a rank, then it skips. | 1, 2, 2, 4 |
dense_rank() | Ties share a rank, no gaps. | 1, 2, 2, 3 |
ntile(4) | Splits rows into N equal buckets. | 1, 1, 2, 2 … |
Offset functions: LAG and LEAD
lag() and lead() read a value from an earlier or later row in the ordered window. This is the standard way to compare each row to the one before it, such as month-over-month change.
with daily_sales as (
select o.order_date,
sum(oi.quantity * oi.unit_price) as daily_total
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by o.order_date
)
select order_date,
daily_total,
lag(daily_total) over (order by order_date) as prev_day,
daily_total - lag(daily_total) over (order by order_date) as change
from daily_sales
order by order_dateBoth take an optional offset and default: lag(daily_total, 1, 0) looks back one row and returns 0 instead of NULLat the start of the partition.
Aggregates as windows
Any aggregate, including sum, avg, count,min, and max, becomes a window function when you addOVER. Without a frame, it totals the whole partition; with anORDER BY it produces a running total.
select product_name,
price,
sum(price) over (partition by category_id) as category_total,
round(100.0 * price / sum(price) over (partition by category_id), 1) as pct_of_category,
sum(price) over (order by price rows between unbounded preceding and current row) as running_total
from productsrows between unbounded preceding and current row means "every ordered row up to this one," which gives you a running total. Leave the frame off and an ordered window still defaults to that range, so a plainsum(...) over (order by ...) already accumulates.
Where windows run in a query
Window functions are evaluated afterWHERE,GROUP BY, and HAVING, but beforeORDER BY and LIMIT. That is why you cannot reference a window function's alias in WHERE. Filter it in an outer query, a CTE, or with QUALIFY.
with ranked as (
select product_name,
category_id,
row_number() over (partition by category_id order by price desc) as rn
from products
)
select product_name, category_id
from ranked
where rn = 1DuckDB (which powers SQLShed) supports QUALIFY, which filters on a window result without the extra CTE:… qualify row_number() over (…) = 1. It is not standard SQL, so reach for the CTE form on databases that lack it.