Stop 03 · SQL for Data AnalysisTopics · 7/10

Window Functions

In short

Ranking students, running totals, 'compared to last month': these need a calculation across rows without squashing them into one. Window functions do exactly that, and they're an interview favourite.

Functions like ROW_NUMBER, RANK, and running totals that calculate across a set of rows related to the current row, without collapsing them the way GROUP BY does.

The jaadu hands out ribbons to a line of small runners at a finish line.

Think of it like this

Sports day ribbons

At sports day, every runner keeps their own place in the line, but each also gets a rank ribbon based on everyone's times. GROUP BY would merge them into one 'average runner'. A window function hands out ribbons while everyone keeps their own row.

Watch it happen

Running total with SUM() OVER

120

200

150

1/5Daily sales for five days. We want a running total, but still one row per day.

The big ideas

What you need to know

Rows are kept

Unlike GROUP BY, a window function adds a column and every original row stays.

OVER defines the window

PARTITION BY splits into groups, ORDER BY sets the order inside each group.

Ranking functions

ROW_NUMBER gives 1, 2, 3. RANK leaves gaps after ties. DENSE_RANK doesn't.

LAG and LEAD

Peek at the previous or next row, perfect for 'change vs last month'.

See it in code

Walk through the code

sql

1SELECT2  city,3  order_month,4  revenue,5  SUM(revenue) OVER (PARTITION BY city ORDER BY order_month) AS running_total,6  revenue - LAG(revenue) OVER (PARTITION BY city ORDER BY order_month) AS change_vs_prev,7  RANK() OVER (PARTITION BY order_month ORDER BY revenue DESC) AS city_rank8FROM monthly_city_revenue;

1/3Running total restarts for each city because of PARTITION BY city.

For each city and month: a running total, the change from last month, and the city's rank that month.

Save yourself the pain

Common mistakes

Oops

Trying to filter WHERE city_rank = 1 in the same query.

Do this instead

Window results aren't ready during WHERE. Wrap it in a CTE, then filter in the outer query.

Oops

Using ROW_NUMBER when ties should share a rank.

Do this instead

Use RANK or DENSE_RANK if two students with the same marks should both be first.

Your mission

Rank and run

~30 min

Quick check

Two students tie for first. Which function gives ranks 1, 1, 2?

Go deeper

1Use ROW_NUMBER() to find the most recent order per customer.
2Practice a running total using SUM() OVER (ORDER BY ...).
3Learn the OVER (PARTITION BY ...) syntax — it's the key to most window function power.