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.

Run this example in the free online PySpark compiler

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

• Deduplicate User Login Sessions
• Calculate DAU, WAU, MAU for an E-commerce Platform

Open the free PySpark compiler · Data Engineering challenges · Data Engineering jobs