SQL Window Functions
Compute across a group of rows — without collapsing them the way GROUP BY does.
What & Why
A window function calculates across related rows while preserving every input row in the output. Windows add group context to row-level data, which is essential for ranking, running totals, and period comparisons.
See How It Works
BUSINESS QUESTION
Rank each user by signup recency within their country.
| id | created_at | country | channel | plan | activated_at | churned_at |
|---|---|---|---|---|---|---|
| 101 | 2024-01-15 09:10:00+00 | US | organic | pro | 2024-01-16 14:25:00+00 | NULL |
| 102 | 2024-02-10 11:05:00+00 | CA | paid | free | NULL | 2024-03-20 10:00:00+00 |
| 103 | 2024-03-12 16:35:00+00 | GB | referral | pro | 2024-03-13 08:15:00+00 | NULL |
| 104 | 2024-04-01 13:20:00+00 | US | organic | free | 2024-04-03 12:00:00+00 | NULL |
EXAMPLE QUERY
SELECT
id,
country,
created_at,
ROW_NUMBER() OVER (
PARTITION BY country
ORDER BY created_at DESC, id
) AS country_signup_rank
FROM growth.users
ORDER BY country, country_signup_rank;RESULT — signup rank within country
| id | country | created_at | country_signup_rank |
|---|---|---|---|
| 102 | CA | 2024-02-10 11:05:00+00 | 1 |
| 103 | GB | 2024-03-12 16:35:00+00 | 1 |
| 104 | US | 2024-04-01 13:20:00+00 | 1 |
| 101 | US | 2024-01-15 09:10:00+00 | 2 |
ROW_NUMBER restarts for each country while preserving every user row.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario