Top users with ROW_NUMBER

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.

Exercise Window ranking

Rank users by event count with ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC).

Show example solution

SELECT user_id, COUNT(*) AS events,
  ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;

Test yourself

How is the top user found?
- ROW_NUMBER + DESC
- SUM + GROUP
- COUNT + ASC
- AVG + HAVING
Show answer

ROW_NUMBER + DESC — ROW_NUMBER with descending order ranks the top.

Add ROW_NUMBER

SELECT user_id, COUNT(*) AS events,
  ____() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;

Show answer

SELECT user_id, COUNT(*) AS events,
  ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM events
GROUP BY user_id;

Top users with ROW_NUMBER: match the terms

ROW_NUMBER

rank
ORDER BY DESC

descending
top user

first
COUNT

count

Next lesson: Growth with LAG

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies