The Loudest Voices

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

Meta

Problem

A member's activity in chat_msgs counts both sides of a conversation, every message they sent plus every reply aimed at them, with ties going to the lower member id. A community manager wants the ten most active members, busiest first.

In every channel, a few names never stop scrolling.

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

What this is really asking

This is a two-role tally wearing a leaderboard costume. A member appears in chat_msgs in two places: as sender_id on the messages they wrote and as reply_to on the replies aimed at them, so no single grouping key sees their whole activity. Anyone can count senders. What separates candidates is fusing the second role cleanly. Drop the reply_to IS NOT NULL guard and the 152 messages that answer nobody become a phantom NULL member sitting at the top of the board. And on this data fifty members tie at four, so the cut at ten is decided entirely by the tie rule; skip it and you hand back an arbitrary ten.

Build it in passes

Step 1: Count each role on its own

Tally sender_id over every row and reply_to over the replies, each down to one row per member with COUNT(*). A reply row feeds both passes: it is a message its sender wrote and activity aimed at its target, which is exactly the double credit the definition asks for.

Step 2: Guard the reply pass

WHERE reply_to IS NOT NULL runs before the second GROUP BY. Without it every unthreaded message lands in one NULL group, and at 152 rows that group outranks every real member and pushes one off the board.

Step 3: Stack with UNION ALL, then add

UNION ALL keeps both partial counts for a member. The outer GROUP BY user_id with SUM(msg_count) adds them into one total_messages.

Step 4: Make the cut at ten deterministic

Sort by total_messages descending, then by user_id, so tied members fall in id order and the same ten always land on the board before LIMIT 10 cuts.

WITH user_msgs AS (
  SELECT sender_id AS user_id, COUNT(*) AS msg_count
  FROM chat_msgs
  GROUP BY sender_id
  UNION ALL
  SELECT reply_to AS user_id, COUNT(*) AS msg_count
  FROM chat_msgs
  WHERE reply_to IS NOT NULL
  GROUP BY reply_to
)
SELECT user_id, SUM(msg_count) AS total_messages
FROM user_msgs
GROUP BY user_id
ORDER BY total_messages DESC, user_id
LIMIT 10

Common follow-up questions

Common follow-up questions

• If `reply_to` stored a message id instead of a user id, how would the query change? (Self-join `chat_msgs` on `reply_to = msg_id` to recover the parent message's `sender_id`, then credit that sender.)
• How would you scope this to a single channel or time window? (Push the filter on `channel` or `sent_at` into both passes so each scan shrinks before it is counted.)
• What if you needed sent and received totals as separate columns, not summed? (Tag each pass with a role label before `UNION ALL`, then `SUM(CASE WHEN role = 'sender' THEN msg_count END)` and its receiver twin per member.)