# Every Voice That Answered > Replies pile up fast. Find out how many distinct voices actually spoke to each piece. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Netflix ## Problem In the chat data, each message can be a reply to content. For each content reference, count unique reporters identified by combining sender and channel. Only count messages that are replies (reply_to is not null). Return the content ID and the reporter count. ## 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 24 rows: | content_id | reporter_count | | --- | --- | | 5 | 1 | | 11 | 1 | | 14 | 1 | | 17 | 1 | | 23 | 1 | ## Worked solution and explanation This is a composite-key distinct count dressed up as content moderation. A 'reporter' is not a person. It is a (`sender_id`, `channel`) pair, and the whole problem is whether you notice that before you type `COUNT`. Anyone can write `GROUP BY reply_to`. What separates candidates is **the key inside the `DISTINCT`**. Count rows and one chatty user replying five times becomes five reporters. Count `sender_id` alone and the same user flagging from '#incidents' and '#engineering' collapses into one. Forget `reply_to IS NOT NULL` and 70% of the table piles into a single `NULL` content group that no one reported on. ### Where the count goes wrong **What most people write** `COUNT(*)` or `COUNT(sender_id)` counts reply messages, so repeat replies inflate the number. `COUNT(DISTINCT sender_id)` fixes the repeats but merges one sender's replies across different channels into one reporter. **What the definition demands** `COUNT(DISTINCT sender_id || '-' || channel)` dedupes on the exact identity the prompt defines. Repeats from the same pair count once, and the same sender in two channels counts twice. > **Skipping the filter mints a phantom content item** > > Without `WHERE reply_to IS NOT NULL`, `GROUP BY` puts every non-reply into one group keyed `NULL`. With a 0.7 null fraction on 35M rows, that is a single output row covering roughly 24.5M messages, with a reporter count in the millions. It looks like the most-reported item in the system. ### Building it #### Step 1: Keep only replies Filter with `reply_to IS NOT NULL` first. A non-reply references no content, so it has nothing to be counted against. Put the filter in `WHERE` so the rows never reach the aggregate. Pushing it to `HAVING` does the same grouping work and then throws it away. #### Step 2: Group by the target, not the message `GROUP BY reply_to`, aliased as `content_id`. Each output row is one piece of content, however many replies point at it. #### Step 3: Build the reporter identity, then dedupe it Concatenate `sender_id || '-' || channel` into one string and wrap it in `COUNT(DISTINCT ...)`. This engine has no multi-column `DISTINCT` inside `COUNT`, so string building is how you give the aggregate a two-column key. **Distinct (`sender_id`, `channel`) pairs per `reply_to`** ```sql SELECT reply_to AS content_id, COUNT(DISTINCT sender_id || '-' || channel) AS reporter_count FROM chat_msgs WHERE reply_to IS NOT NULL GROUP BY reply_to ``` > **The separator is what makes the key honest** > > Without the '-', sender 1 in channel '23' and sender 12 in channel '3' both become '123'. With it, collisions are impossible here: `sender_id` is an integer and can never contain '-', so the first '-' always marks the boundary. > **Read the definition of the entity out loud** > > The senior move is saying "reporter means sender plus channel, so my distinct key has two parts" before writing anything. Candidates who jump straight to `COUNT(DISTINCT sender_id)` usually never check their answer against the prompt's definition. > **One scan, one hash aggregate** > > No date predicate means no pruning across the 365 `sent_at` partitions, so this is a full scan of about 18GB. The filter cuts 35M rows to about 10.5M before the aggregate. `COUNT(DISTINCT ...)` then keeps a per-group set of keys across up to 9M `reply_to` values. That is memory-heavy but still a single pass. Nothing is joined or scanned twice. ## Common follow-up questions - Your engine supports `COUNT(DISTINCT (sender_id, channel))`. Would you still concatenate? _(Tests whether you see the concatenation as a workaround rather than the idea itself. A tuple key avoids building strings and the separator question disappears.)_ - Return only content flagged by more than 5 reporters, busiest first. _(`HAVING` on the aggregate plus `ORDER BY reporter_count DESC`. Checks that you know where post-aggregate filters belong.)_ - What if `channel` could be `NULL`? _(`||` with `NULL` yields `NULL`, and `COUNT(DISTINCT ...)` silently drops that reporter. Wrap it in `COALESCE(channel, '')` or decide explicitly how those replies should count.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/every_voice_that_answered) - [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.