Threads Excluding User

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Meta

Problem

The trust and safety team is auditing reply activity in chat_msgs, where one conversation is a single sender_id paired with the channel they posted in. Looking only at reply messages (those with a non-null reply_to), count how many different sender-and-channel conversations there are.

Every thread they're not part of.

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 really is

This is a distinct composite-key count wearing a trust-and-safety costume. Strip the business words and the ask is: build one key per (sender_id, channel) pair, then count the distinct keys, but only over the rows that are actually replies. Anyone can COUNT the table. Two things separate the answers. You have to dedup the pair, because the same sender keeps posting in the same channel and raw rows overcount wildly. And you have to drop the roughly 70 percent of rows that are not replies, because they carry sender-channel pairs that never answered anyone. Miss either and the audit reports a number that is confidently wrong.

The two things that bite

Step 1: The non-reply majority has to go

reply_to is null on about 70 percent of the 30M rows: those are original messages, not replies. If you leave them in, every sender-and-channel pair they touch enters the count, even pairs that never once replied. The filter is not cosmetic. It is the difference between counting reply conversations and counting all activity. Write WHERE reply_to IS NOT NULL and the non-reply majority is gone.

Step 2: Rows are not conversations

A busy sender posts twenty-odd times, often in the same channel, so the raw row count of replies has nothing to do with the number of conversations. Wrapping the composite key in COUNT(DISTINCT ...) collapses those repeats: one sender-and-channel pairing counts once no matter how many reply messages it spans. Drop the DISTINCT and you are back to counting messages.

Step 3: Mind the seam between int and string

sender_id is an integer and channel is a string. Glue them with nothing between and (sender 197, channel '#a') can, in the general case, collide with a differently split pair. A single non-numeric delimiter between the two values keeps the boundary unambiguous. Here the channels all start with '#', so a plain '-' is safe, but making the separator explicit is the habit that survives a schema where it is not.

The solution

SELECT COUNT(DISTINCT sender_id || '-' || channel) AS conversation_count
FROM chat_msgs
WHERE reply_to IS NOT NULL

Common follow-up questions

• How would you report the number of distinct conversations broken down by channel? (Extends the pattern with GROUP BY: SELECT channel, COUNT(DISTINCT `sender_id` || '-' || channel) ... GROUP BY channel, or more simply COUNT(DISTINCT `sender_id`) per channel.)
• If you had accidentally concatenated `reply_to` into the key, why would the IS NOT NULL filter suddenly look redundant? (Tests whether they see COUNT ignores nulls: a null composite key from a null side is silently dropped, so the filter and the concatenation interact.)
• Construct a pair of rows where dropping the delimiter undercounts the conversations. (Tests recognition of the collision bug: without a delimiter, (sender 12, channel '3x') and (sender 1, channel '23x') both build '123x' and merge into one distinct value.)
• Suppose trust and safety instead wants conversations keyed by the parent message replied to. How does the query change? (Tests the symmetric-identity idea: if a conversation should be the parent message rather than the sender-channel pair, the key becomes `reply_to` and the metric changes shape.)