# Two-Way Street > A conversation reads the same from either side. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Meta ## Problem In chat_msgs a reply stores its author in sender_id and the user being answered in reply_to, which stays empty on messages that are not replies, and the community team treats a reply between two users as one conversation whichever of them sent it. Report how many unique two-person conversations there are and how many different users wrote at least one reply. ## 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 1 of 1 rows: | conversation_count | replying_users | | --- | --- | | 24 | 24 | ## Worked solution and explanation ### What this problem really is Strip the chat costume and this is two unique counts that must agree on which rows they read. The conversation count looks like the hard part, and the smaller-id-first key is the right way to define a pair. On its own, though, it is forgiving: a non-reply has an empty `reply_to`, the two-argument `MIN` turns its key into `NULL`, and `COUNT(DISTINCT ...)` skips it whether you filter or not. The replier count is not forgiving. `COUNT(DISTINCT sender_id)` over the whole log counts everyone who ever posted: **50 instead of 24**. The filter belongs to the question, not to one column. #### Step 1: Restrict the log to replies `chat_msgs` holds 200 messages and only 48 carry a `reply_to`. Both numbers are about replies, so `WHERE reply_to IS NOT NULL` scopes the whole query. Without it, the conversation count happens to survive and `replying_users` silently becomes a count of everyone. #### Step 2: Key each pair smaller id first `MIN(sender_id, reply_to)` and `MAX(sender_id, reply_to)` are the two-argument scalar forms. A reply from 876 to 197 and one from 197 to 876 both become '197,876'. The comma keeps 1,23 from colliding with 12,3. #### Step 3: Count unique keys and unique authors Every exchange in this log is recorded twice, so `COUNT(*)` would report 48 conversations. `COUNT(DISTINCT ...)` on the key gives 24, and `COUNT(DISTINCT sender_id)` on the same filtered rows gives the 24 users who replied at least once. ### The solution **Pair key and repliers over reply rows** ```sql SELECT COUNT(DISTINCT MIN(sender_id, reply_to) || ',' || MAX(sender_id, reply_to)) AS conversation_count, COUNT(DISTINCT sender_id) AS replying_users FROM chat_msgs WHERE reply_to IS NOT NULL ``` **Filter only the pair count** `COUNT(DISTINCT sender_id)` with no `WHERE` returns 50: the 26 users who only posted non-replies are counted as repliers. The conversation count still reads 24, which is exactly why this bug passes a glance. **Filter the whole query** `WHERE reply_to IS NOT NULL` scopes both counts to the 48 reply rows: 24 conversations, 24 replying users. > **Not every engine nulls the key** > > The two-argument `MIN` returns `NULL` when either side is `NULL`, which is why the pair count survives without the filter here. Postgres `LEAST` and `GREATEST` skip nulls, so the same query there turns each non-reply into a self-pair like '876,876'. Write the filter and the count is right on every engine. > **Direction never flips on this log** > > No two users in `chat_msgs` answered each other in both directions, so a raw `sender_id || ',' || reply_to` key also returns 24 here. The smaller-id-first key is what the definition of a conversation demands, and it keeps the number honest the first week two users reply back and forth. > **One scope for one question** > > Strong candidates state the reply filter once and let both counts inherit it. Patching a filter into one aggregate while leaving the other on the whole table is the tell of someone checking numbers, not reading the question. > **One scan, two hash sets** > > A single pass over the table, a cheap `MIN`/`MAX` and concatenation per reply row, and two distinct sets. Memory is bounded by unique pairs and unique authors, not by message volume. ## Common follow-up questions - How would you count messages per conversation? _(Turn the pair key into the grouping column instead of counting it.)_ - What changes once users start replying to each other in both directions? _(Tests whether the candidate sees the normalized key earning its keep.)_ - What if `reply_to` held a message id rather than a user id? _(The pair then needs the replied-to message's author: a self-join on the message id.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/two_way_street) - [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.