Window function
Also: analytic function · OVER clause
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.
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 the single most-tested advanced SQL topic in analytics-engineering interviews, and they show up constantly in real models. If you only drill one advanced skill, drill these.
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're also the make-or-break topic in live SQL screens. Fluency here is one of the clearest signals of an interview-ready candidate.
- 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.
Learn this by building, not memorizing.
Definitions get you the vocabulary. The platform gets you the skill — graded exercises, real projects, and a portfolio capstone.