Top users with ROW_NUMBER
- SQL course
- Advanced
- Lesson 21/81
- 5 min
Find top users with ROW_NUMBER.
Window ranking
You can rank the most active users with ROW_NUMBER().
SELECT user_id, COUNT(*) AS events,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;This query puts the most active user first.
user_id, COUNT(*) AS events, ROW_NUMBER() OVER (ORDER BY … is selected from events. Rows are grouped by user_id; aggregate functions are computed per group. ROW_NUMBER() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
Window ranking — details
ROW_NUMBER OVER (ORDER BY ... DESC) puts the highest count first.
Filtering on this number with WHERE rn <= 3 reports the three busiest days; to filter you need to wrap the query in a CTE.
Practice Window ranking
Rank users by event count with ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC).
Starter code
-- top users
SELECT user_id, COUNT(*) AS events,
ROW_NUMBER()Sample solution
SELECT user_id, COUNT(*) AS events,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;Expected output
user_id events rk 5 42 1 3 35 2
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 How is the top user found?
- ROW_NUMBER + DESC Correct answer
- SUM + GROUP
- COUNT + ASC
- AVG + HAVING
Explanation: ROW_NUMBER with descending order ranks the top.
2 Add ROW_NUMBER
SELECT user_id, COUNT(*) AS events,
____() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;SELECT user_id, COUNT(*) AS events,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;Explanation: ROW_NUMBER is the right answer.
3 Top users with ROW_NUMBER: match the terms
| Term | Meaning |
|---|---|
ROW_NUMBER |
rank |
ORDER BY DESC |
descending |
top user |
first |
COUNT |
count |