Every Voice That Answered

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

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.

Replies pile up fast. Find out how many distinct voices actually spoke to each piece.

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 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.

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.

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

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.)