SQL Top N per Group
One of the most common real interview questions — and it requires a step window functions can't do alone.
What & Why
The top-N-per-group pattern assigns a partitioned rank and keeps only ranks up to N. It answers questions like the two highest-activity rows per group without running a separate query for every group.
See How It Works
BUSINESS QUESTION
Return the two highest-page-view sessions for every device type.
| id | user_id | started_at | ended_at | page_views | device |
|---|---|---|---|---|---|
| 5001 | 101 | 2024-01-16 14:20:00+00 | 2024-01-16 14:52:00+00 | 8 | desktop |
| 5002 | 102 | 2024-02-10 11:10:00+00 | 2024-02-10 11:18:00+00 | 3 | mobile |
| 5003 | 103 | 2024-03-13 08:10:00+00 | 2024-03-13 09:04:00+00 | 12 | desktop |
| 5004 | 104 | 2024-04-03 12:00:00+00 | NULL | 1 | tablet |
| 5005 | 105 | 2024-04-04 09:00:00+00 | 2024-04-04 09:21:00+00 | 7 | mobile |
| 5006 | 106 | 2024-04-05 10:15:00+00 | 2024-04-05 10:42:00+00 | 5 | tablet |
| 5007 | 107 | 2024-04-06 13:05:00+00 | 2024-04-06 13:38:00+00 | 10 | desktop |
Watch every session rank, then keep two per deviceStep 1 of 2
ROW_NUMBER() OVER (PARTITION BY device ORDER BY page_views DESC, id)
| session_id | device | page_views | view_rank |
|---|---|---|---|
| 5003 | desktop | 12 | 1 |
| 5007 | desktop | 10 | 2 |
| 5001 | desktop | 8 | 3 |
| 5005 | mobile | 7 | 1 |
| 5002 | mobile | 3 | 2 |
| 5006 | tablet | 5 | 1 |
| 5004 | tablet | 1 | 2 |
The inner query ranks all seven sessions and restarts numbering for each device.
EXAMPLE QUERY
WITH ranked AS (
SELECT
id AS session_id,
device,
page_views,
ROW_NUMBER() OVER (
PARTITION BY device
ORDER BY page_views DESC, id
) AS view_rank
FROM growth.sessions
)
SELECT session_id, device, page_views, view_rank
FROM ranked
WHERE view_rank <= 2
ORDER BY device, view_rank;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario