Reference

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 way GROUP BY does. Every input row stays in the output, with the computed value added as a new column.

Basic shape

select product_name,
       category_id,
       price,
       row_number() over (
         partition by category_id
         order by price desc
       ) as price_rank
from products

Practice

Practice this with the interactive window function lesson Practice ROW_NUMBER, RANK, and DENSE_RANK on tied rows Practice LAG and LEAD on a customer's orders Practice running totals with SUM() OVER Practice window frames: ROWS vs the default Practice percent of total with a window Practice month-over-month change with LAG Practice a rolling 7-day window with RANGE

Reading the OVER clause

The function name says what to calculate; the OVER clause 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.

Rank products within each category

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()`	Distinct per partition; tied rows are numbered in 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.

Compare each day to the previous day

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_date

Both take an optional offset and default: lag(daily_total, 1, 0) looks back one row and returns 0 instead of NULL at the start of the partition.

Aggregates as windows

Any aggregate, including sum, avg, count, min, and max, becomes a window function when you add OVER. With no ORDER BY, DuckDB's default frame is the whole partition. Add an ORDER BY and the default becomes a cumulative RANGE frame through the current row and its peers.

Share of category total, and 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 products

Frames in one line

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "every ordered row through this row," which gives a row-by-row running total. If you omit the frame but keep ORDER BY, DuckDB defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That also accumulates, but tied ordering values are treated as peers and share the same frame.

Where windows run in a query

Window functions are evaluated after WHERE, GROUP BY, and HAVING, but before ORDER 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.

Keep only the top product per category

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 = 1

DuckDB shortcut

DuckDB (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.

Lesson

Top-N Reports in SQL

Build ranked summary reports.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview

SQL Window Function Interview Questions

Practice ranking, running totals, and top row per group.

Mistake

SQL: Window Running Totals Can Jump on Ties

The default RANGE frame groups tied rows; use ROWS.