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.

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