Professional level · Lesson 18 of 20

Window functions: rankings and totals without losing rows

ROW_NUMBER, RANK, PARTITION BY and running totals.

12 minutes2 games

GROUP BY squashes each group into one row. Sometimes you want every row, each with something computed from other rows: its rank, its city's average, the running total. That's what window functions do, and their sign is the word OVER.

SQL

SELECT name,
       xp,
       RANK() OVER (ORDER BY xp DESC) AS place,
       ROUND(AVG(xp) OVER ()) AS overall_avg
FROM students
WHERE xp IS NOT NULL
ORDER BY place;

OVER (ORDER BY xp DESC) means "sort all rows by XP and give each its position". An empty OVER () means "the window is every row". Notice all 7 rows are still there; nothing was squashed.

PARTITION BY: per group

SQL

SELECT city,
       name,
       xp,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY xp DESC) AS rank_in_city
FROM students
ORDER BY city, rank_in_city;

xp	ROW_NUMBER	RANK	DENSE_RANK
500	1	1	1
400	2	2	2
400	3	2	2
300	4	4	3

The difference shows on ties: ROW_NUMBER never repeats, RANK repeats and leaves a gap, DENSE_RANK repeats without a gap.

Running totals

SQL

-- How many enrollments had we reached by each date?
SELECT enrolled_at,
       COUNT(*) OVER (ORDER BY enrolled_at) AS total_so_far
FROM enrollments
ORDER BY enrolled_at;

What's the output?

SELECT name,
       ROW_NUMBER() OVER (ORDER BY xp DESC) AS n
FROM students
WHERE city = 'Cairo'
ORDER BY n;

What will this code print?

Exercise

Get the student with the highest XP in each city: city, name and xp, ordered by city.

Expected result:

city | name | xp
Alexandria | Karim | 150
Cairo | Laila | 320
Giza | Nour | 410
Mansoura | Hana | 60

SQL

WITH ranked AS (
  SELECT city, name, xp,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY xp DESC) AS r
  FROM students
)
SELECT

Learn more from

Played 0 of 2 games

SQL