SQL window functions with examples
SQL window functions perform a calculation across a set of rows related to the current row using the OVER() clause, without grouping the rows into one. PARTITION BY splits rows into groups, ORDER BY orders them inside the window, and functions like ROW_NUMBER(), RANK(), LAG(), LEAD() and running SUM() operate over that frame.
The OVER clause
Every window function ends with OVER (PARTITION BY ... ORDER BY ...). PARTITION BY is optional (whole result set if omitted); ORDER BY defines row order and, for aggregates, the running frame.
Deduplicate with ROW_NUMBER
A common pattern: assign ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts) and keep rn = 1 to pick the first/latest row per key.
The OVER clause and what makes a window function different
A window function computes a value across a set of rows related to the current row, but unlike GROUP BY it does not collapse them — every input row stays in the output, with the calculated value attached. That is what makes it the right tool for running totals, per-group rankings, and comparisons against a group average. The OVER clause defines the window: PARTITION BY restarts the calculation per group, ORDER BY sequences rows inside it, and the frame clause narrows which rows contribute. SUM(sales) OVER (PARTITION BY region ORDER BY sale_date) gives a running total per region.
Frames, ranking functions and where they are allowed
ROWS BETWEEN counts physical rows and RANGE BETWEEN works on the ordering values, so ties are grouped together — a distinction that changes the answer whenever the order column has duplicates. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row rolling window. For ranking, ROW_NUMBER() is always unique, RANK() leaves gaps after ties and DENSE_RANK() does not; LAG() and LEAD() reach to the previous or next row for period-over-period comparisons. One rule trips people up constantly: window functions are evaluated after WHERE and GROUP BY, so you cannot filter on one in the same WHERE clause. Wrap the query in a CTE or subquery and filter outside it — which is exactly why top-N-per-group queries always have two levels.
Example (SQL)
SELECT
region,
sale_date,
amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date) AS rn,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales
ORDER BY region, sale_date;Numbers rows per region and computes a per-region running total of amount ordered by date.
Frequently asked questions
What is a window function in SQL?
A function that computes a value over a window of rows related to the current row via OVER(), without collapsing the rows the way GROUP BY does.
What does PARTITION BY do in a window function?
It divides the result set into independent groups; the window function restarts for each partition, similar to GROUP BY but keeping all rows.
How do I get the latest row per group in SQL?
Use ROW_NUMBER() OVER (PARTITION BY group ORDER BY timestamp DESC) and filter to rn = 1.
Practice challenges
Open the free PySpark compiler · Data Engineering challenges · Data Engineering jobs