Window functions

1711 min

GROUP BY folds rows into one. Window functions leave every row in place and add a calculation over its group next to it: a rank, a running total, the value from the previous row.

The pattern

function() OVER (PARTITION BY group ORDER BY order)

PARTITION BY — which groups to calculate within (without it, the whole result is one group);
ORDER BY — the order inside each group.

Ranks: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

university· Try changing it

SELECT first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM teachers
LIMIT 10;

ROW_NUMBER — 1, 2, 3… even equal values get different numbers;
RANK — equal values share a rank and the next one skips (1, 1, 3);
DENSE_RANK — equal values share a rank with no gap (1, 1, 2).

Within each group

The best-paid teacher of every faculty:

university· Try changing it

WITH ranked AS (
  SELECT faculty_id, first_name, last_name, salary,
         RANK() OVER (PARTITION BY faculty_id ORDER BY salary DESC) AS rnk
  FROM teachers
)
SELECT faculty_id, first_name, last_name, salary FROM ranked WHERE rnk = 1;

A window function cannot be used in WHERE, so we calculate it in a CTE first and filter afterwards.

Aggregate + `OVER`: shares and running totals

shop· Try changing it

SELECT c.name, COUNT(*) AS products,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share
FROM products p JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY products DESC;

SUM(...) OVER () is the total of the whole result, shown next to every row.

A running total of revenue by month:

taxi· Try changing it

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, SUM(fare) AS revenue
  FROM trips
  WHERE status = 'completed' AND requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, revenue, SUM(revenue) OVER (ORDER BY mon) AS running_total
FROM m ORDER BY mon;

`LAG` and `LEAD` — the neighbouring row

LAG(x) is the value from the previous row and LEAD(x) from the next one. Change compared with the previous month:

taxi· Try changing it

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, COUNT(*) AS trips
  FROM trips WHERE requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, trips, trips - LAG(trips) OVER (ORDER BY mon) AS diff
FROM m ORDER BY mon;

1Exercise

Show each employee’s first_name, last_name, salary and their salary rank across the whole company (DENSE_RANK, highest = 1), by rank ascending.

2Exercise

Show each student’s id, first_name and their position within their own group: whoever enrolled first (enrolled_at) is first, and on the same date the smaller id comes first (ROW_NUMBER, PARTITION BY group_id).

Remember

A window function does not fold rows — it adds a calculation to each row.
PARTITION BY is the group, ORDER BY is the order.
To filter on the result, wrap it in a CTE or subquery first.

Window functions

1711 min

GROUP BY folds rows into one. Window functions leave every row in place and add a calculation over its group next to it: a rank, a running total, the value from the previous row.

The pattern

function() OVER (PARTITION BY group ORDER BY order)

PARTITION BY — which groups to calculate within (without it, the whole result is one group);
ORDER BY — the order inside each group.

Ranks: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

university· Try changing it

SELECT first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM teachers
LIMIT 10;

ROW_NUMBER — 1, 2, 3… even equal values get different numbers;
RANK — equal values share a rank and the next one skips (1, 1, 3);
DENSE_RANK — equal values share a rank with no gap (1, 1, 2).

Within each group

The best-paid teacher of every faculty:

university· Try changing it

WITH ranked AS (
  SELECT faculty_id, first_name, last_name, salary,
         RANK() OVER (PARTITION BY faculty_id ORDER BY salary DESC) AS rnk
  FROM teachers
)
SELECT faculty_id, first_name, last_name, salary FROM ranked WHERE rnk = 1;

A window function cannot be used in WHERE, so we calculate it in a CTE first and filter afterwards.

Aggregate + `OVER`: shares and running totals

shop· Try changing it

SELECT c.name, COUNT(*) AS products,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share
FROM products p JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY products DESC;

SUM(...) OVER () is the total of the whole result, shown next to every row.

A running total of revenue by month:

taxi· Try changing it

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, SUM(fare) AS revenue
  FROM trips
  WHERE status = 'completed' AND requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, revenue, SUM(revenue) OVER (ORDER BY mon) AS running_total
FROM m ORDER BY mon;

`LAG` and `LEAD` — the neighbouring row

LAG(x) is the value from the previous row and LEAD(x) from the next one. Change compared with the previous month:

taxi· Try changing it

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, COUNT(*) AS trips
  FROM trips WHERE requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, trips, trips - LAG(trips) OVER (ORDER BY mon) AS diff
FROM m ORDER BY mon;

1Exercise

Show each employee’s first_name, last_name, salary and their salary rank across the whole company (DENSE_RANK, highest = 1), by rank ascending.

2Exercise

Remember

A window function does not fold rows — it adds a calculation to each row.
PARTITION BY is the group, ORDER BY is the order.
To filter on the result, wrap it in a CTE or subquery first.

SQLab

Window functions

The pattern

Ranks: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Within each group

Aggregate + `OVER`: shares and running totals

`LAG` and `LEAD` — the neighbouring row

Remember

Window functions

The pattern

Ranks: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Within each group

Aggregate + `OVER`: shares and running totals

`LAG` and `LEAD` — the neighbouring row

Remember

SQLab

Window functions

The pattern

Ranks: ROW_NUMBER, RANK, DENSE_RANK

Within each group

Aggregate + OVER: shares and running totals

LAG and LEAD — the neighbouring row

Remember

Window functions

The pattern

Ranks: ROW_NUMBER, RANK, DENSE_RANK

Within each group

Aggregate + OVER: shares and running totals

LAG and LEAD — the neighbouring row

Remember

Ranks: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Aggregate + `OVER`: shares and running totals

`LAG` and `LEAD` — the neighbouring row

Ranks: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Aggregate + `OVER`: shares and running totals

`LAG` and `LEAD` — the neighbouring row