Most Active Chat Users

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Airbnb

Problem

Each row of chat_msgs is one message sent by sender_id, and its reply_to is filled only when that message answers an earlier one. Build a reply leaderboard covering every user who has posted, most replies first, with each user's reply count and a standing where equal counts share a place and the next lower count takes the very next number.

The loudest voices on the platform.

Tables

chat_msgs

Columns: msg_id INTEGER, channel TEXT, sender_id INTEGER, content TEXT, msg_type TEXT, sent_at TEXT, edited INTEGER, reply_to INTEGER

Expected output

Worked solution and explanation

This is a standings problem where the real decision is what to count. Every sender_id in chat_msgs has exactly four rows, so a board built on COUNT(*) puts all 50 users at standing 1 and says nothing. The signal lives in reply_to: COUNT(reply_to) skips the empty ones and splits the users into 24 with two replies and 26 with none. Then the standing has to be gap-free. Standard ranking hands the zero-reply tier a 25 instead of a 2, and filtering to replies first quietly erases those 26 users from a board that was supposed to cover everyone.

Walk it before you write it

Step 1: Count filled reply_to values per sender

COUNT(reply_to) counts only rows where the column is not NULL, which is exactly the definition of a reply here. COUNT(*) would count every message, and on this data that is 4 for everybody.

Step 2: Keep every sender on the board

Total over all rows with GROUP BY sender_id and no WHERE. A user whose four messages are all fresh posts still forms a group, and COUNT(reply_to) gives that group 0. Filter first and the group never exists.

Step 3: Hand out gap-free standings over the totals

The window runs after GROUP BY, so DENSE_RANK() OVER (ORDER BY COUNT(reply_to) DESC) sees one row per sender. Tied counts share a number and the next count takes the next integer: 1 for the two-reply users, 2 for the rest.

SELECT DENSE_RANK() OVER (ORDER BY COUNT(reply_to) DESC) AS standing,
    sender_id,
    COUNT(reply_to) AS reply_count
FROM chat_msgs
GROUP BY sender_id
ORDER BY standing

Standard ranking

RANK() gives the 24 two-reply users 1 and the 26 zero-reply users 25, because it counts how many rows sit ahead of them.

Gap-free standing

DENSE_RANK() gives them 1 and 2: it counts how many distinct totals sit ahead, so two levels of activity produce two standings.

Common follow-up questions

• How would the board change to count only replies sent in 2026, while still listing every user? (Tests whether the date condition moves into the count rather than a `WHERE`, so zero-reply users survive.)
• Show only the top three standings, keeping everyone tied at the third. (Tests filtering on `standing` after it is computed, rather than a row `LIMIT`.)
• If `reply_to` sometimes points at a message that no longer exists, should it still count? (Tests whether the candidate would validate the reference against `msg_id`.)