# The Loudest Voices > In every channel, a few names never stop scrolling. Canonical URL: 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. ## Tables ### chat_msgs - msg_id INTEGER - channel TEXT - sender_id INTEGER - content TEXT - msg_type TEXT - sent_at TEXT - edited INTEGER - reply_to INTEGER Sample rows (10 of 200): | msg_id | channel | sender_id | content | msg_type | sent_at | edited | reply_to | | --- | --- | --- | --- | --- | --- | --- | --- | | 147 | #engineering | 197 | Anyone seeing increased latency? | file | 2026-02-02 01:07:00 | 0 | NULL | | 194 | #incidents | 294 | PR #412 ready for review | reaction | 2026-03-03 02:14:00 | 0 | NULL | | 241 | #random | 391 | Dashboard is down | thread_reply | 2026-04-04 03:21:00 | 0 | NULL | | 288 | #deployments | 488 | Fixed the flaky test | system | 2026-05-05 04:28:00 | 0 | NULL | | 335 | #data-team | 585 | Merged the migration | Text | 2026-06-06 05:35:00 | 0 | NULL | | 382 | #general | 682 | NULL | FILE | 2026-07-07 06:42:00 | 0 | NULL | | 429 | #engineering | 779 | LGTM | text | 2026-08-08 07:49:00 | 0 | NULL | | 476 | #incidents | 876 | 👍 | file | 2026-09-09 08:56:00 | 0 | 5 | | 523 | #random | 973 | | reaction | 2026-01-10 09:03:00 | 0 | NULL | | 570 | #deployments | 1070 | Can someone check the pipeline? | thread_reply | 2026-02-11 10:10:00 | 0 | NULL | ## Expected output First 5 of 10 rows: | user_id | total_messages | | --- | --- | | 100 | 4 | | 197 | 4 | | 294 | 4 | | 391 | 4 | | 488 | 4 | ## 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. **The loudest voices** ```sql 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 ``` > **`UNION` eats half of a balanced member** > > `UNION` deduplicates whole rows. A member who sent three messages and received three replies produces `(user_id, 3)` twice, `UNION` keeps one, and their total drops from six to three. `UNION ALL` is not a style choice here. > **The cut falls inside a tie** > > Every one of the 50 senders has exactly four messages, so ten of fifty tied members make the board. The candidate who spots that before writing `LIMIT 10`, and asks how ties break, shows they read the data and not just the prompt. > **Shrink each pass before the union** > > Each pass collapses the scan to one row per member before stacking, so the outer `SUM` touches at most two rows per member. Partitioning by `sent_at` does not help: both passes read the whole table. --- ### 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_loudest_voices) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.