Window function
A SQL function that computes across a set of rows related to the current row — running totals, rankings, period-over-period — without collapsing them into one row like GROUP BY does.
Also: analytic function · OVER clause
On this page · 7 sections
- Category
- SQL
- Also known as
- analytic function, OVER clause
- Goes deeper in
- SQL for Analytics Engineers
A window function performs a calculation over a “window” of rows defined by an `OVER (...)` clause, while keeping every row in the output. That's the key difference from aggregation: `GROUP BY` collapses rows; a window function annotates each row with a value computed from its neighbors.
The everyday uses: `ROW_NUMBER()` and `RANK()` to dedupe or pick the latest record per group; `LAG()`/`LEAD()` for period-over-period comparisons; `SUM() OVER (...)` for running totals. The `PARTITION BY` resets the calculation per group, and `ORDER BY` defines sequence within the window.
Window functions are a common advanced SQL interview topic and a recurring production pattern. Practice ranking, latest-record selection, period comparisons, and running totals, including their ordering and tie behavior.
select *
from (
select *,
row_number() over (
partition by customer_id order by order_date desc
) as rn
from orders
)
where rn = 1A huge share of real analytics questions — latest record per customer, running totals, month-over-month change, ranking within a group — are window-function problems. They're unavoidable on the job.
They are also common in live SQL screens because ordering, partitioning, and tie behavior reveal how a candidate checks query semantics.
- Trying to filter on a window function in WHERE (you can't) instead of wrapping it in a subquery/CTE and filtering on the alias.
- Forgetting PARTITION BY, so the calculation runs over the whole table instead of per group.
- Confusing RANK (gaps on ties) with DENSE_RANK (no gaps) and ROW_NUMBER (always unique).
- What's the difference between a window function and GROUP BY?
- GROUP BY collapses rows into one per group; a window function keeps every row and adds a value computed across a related set of rows (the window). Use windows when you need both the detail rows and an aggregate-like value.