SQL · Lesson 12 of 17

Window Functions

Rank, compare to the previous row, and run totals without collapsing the result.

Advanced
18 min read
3 objectives

Before this lessonLesson 11: CTEs and Recursive Queries

What you will learn

Rank rows with ROW_NUMBER and RANK
Compare rows with LAG and LEAD
Build running totals

Your Progress

0 of 17 lessons 0%

Lessons0 / 17
Completed0
Est. time left~ 4 hours

Create a free account to keep your progress on every device.

Tip: pressing Next marks this lesson complete automatically.

A GROUP BY collapses rows: you get one row per group and lose the detail. A window function computes across a set of rows while keeping every row — which is what you want for rankings, running totals and "compare each row to the one before".

The anatomy of OVER

OVER (PARTITION BY ... ORDER BY ...) defines the window. PARTITION BY splits rows into independent groups (like GROUP BY, but non-destructive) and ORDER BY sets the order inside each one.

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       SUM(salary) OVER () AS company_total
FROM employees
ORDER BY department, salary DESC;

Output

name     | department  | salary | dept_avg           | company_total
---------+------------+-------+-------------------+--------------
Margaret | Design      | 140000 | 120000             | 820000
Alan     | Design      | 110000 | 120000             | 820000
Barbara  | Design      | 110000 | 120000             | 820000
Grace    | Engineering | 190000 | 153333.33333333334 | 820000
Ada      | Engineering | 150000 | 153333.33333333334 | 820000
Linus    | Engineering | 120000 | 153333.33333333334 | 820000

Every employee row survives, and each carries its department average beside it. Doing that with GROUP BY needs a self-join.

Three ways to number rows

ROW_NUMBER() — always 1, 2, 3, even for ties. Arbitrary tie-break.
RANK() — ties share a number, then the sequence jumps (1, 2, 2, 4).
DENSE_RANK() — ties share a number and the sequence does not jump (1, 2, 2, 3).

SELECT name, department, salary,
       ROW_NUMBER()  OVER (ORDER BY salary DESC) AS row_number,
       RANK()        OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Output

name     | department  | salary | row_number | rank | dense_rank
---------+------------+-------+-----------+-----+-----------
Grace    | Engineering | 190000 | 1          | 1    | 1
Ada      | Engineering | 150000 | 2          | 2    | 2
Margaret | Design      | 140000 | 3          | 3    | 3
Linus    | Engineering | 120000 | 4          | 4    | 4
Alan     | Design      | 110000 | 5          | 5    | 5
Barbara  | Design      | 110000 | 6          | 5    | 5

Alan and Barbara both earn 110000. Watch what each function does with that tie — picking the wrong one is the single most common window-function bug.

Top N per group

You cannot filter on a window function in WHERE, because windows are computed after WHERE runs. Compute it in a subquery or CTE, then filter outside.

WITH ranked AS (
  SELECT name, department, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk = 1
ORDER BY department;

Output

name     | department  | salary
---------+------------+-------
Margaret | Design      | 140000
Grace    | Engineering | 190000

LAG and LEAD: the previous and next row

These reach into neighbouring rows of the same window, which turns "did it go up?" from a self-join into one line. LeetCode 197 Rising Temperature is exactly this.

SELECT id, record_date, temperature,
       LAG(temperature)  OVER (ORDER BY record_date) AS yesterday,
       LEAD(temperature) OVER (ORDER BY record_date) AS tomorrow
FROM weather;

Output

id | record_date | temperature | yesterday | tomorrow
---+------------+------------+----------+---------
1  | 2015-01-01  | 10          | NULL      | 25
2  | 2015-01-02  | 25          | 10        | 20
3  | 2015-01-03  | 20          | 25        | 30
4  | 2015-01-04  | 30          | 20        | NULL

WITH compared AS (
  SELECT id, temperature,
         LAG(temperature) OVER (ORDER BY record_date) AS yesterday
  FROM weather
)
SELECT id FROM compared WHERE temperature > yesterday;

Output

id
---
2
4

Running totals

Add ORDER BY to an aggregate window and it accumulates row by row instead of returning one value for the whole partition.

SELECT id, trans_date, amount,
       SUM(amount) OVER (ORDER BY trans_date, id) AS running_total,
       SUM(amount) OVER (PARTITION BY country ORDER BY trans_date, id) AS running_by_country
FROM transactions
ORDER BY trans_date, id;

Output

id  | trans_date | amount | running_total | running_by_country
----+-----------+-------+--------------+-------------------
121 | 2018-12-18 | 1000   | 1000          | 1000
122 | 2018-12-19 | 2000   | 3000          | 3000
123 | 2019-01-01 | 2000   | 5000          | 5000
124 | 2019-01-07 | 2000   | 7000          | 2000
125 | 2019-01-22 | 500    | 7500          | 2500

FIRST_VALUE and NTILE

SELECT name, department, salary,
       FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY salary DESC) AS half
FROM employees;

Output

name     | department  | salary | top_earner | half
---------+------------+-------+-----------+-----
Margaret | Design      | 140000 | Margaret   | 1
Alan     | Design      | 110000 | Margaret   | 2
Barbara  | Design      | 110000 | Margaret   | 2
Grace    | Engineering | 190000 | Grace      | 1
Ada      | Engineering | 150000 | Grace      | 1
Linus    | Engineering | 120000 | Grace      | 2

Key takeaways

A window function keeps every row; GROUP BY collapses them.
PARTITION BY = the groups, ORDER BY inside OVER = the order.
Ties: ROW_NUMBER breaks them, RANK skips, DENSE_RANK does not.
Filter a window result in an outer query — never in the same WHERE.

-- Write your solution here

Finished reading? Mark this lesson complete to track your progress.

Up next · Lesson 13INSERT, UPDATE, DELETEChange data safely, including transactions.