Symmetric Reply Network

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

L3

Asked in

Google

Problem

The chat_msgs table logs team chat activity, where a reply stores the user ID of the person being replied to in reply_to while an original post leaves it empty. Map the symmetric reply network: whenever one person replied to another, list that pair of user IDs in both directions.

Every reply is an edge. Draw it both ways.

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

The disguise

This is a symmetric edge-list build wearing a chat-network costume. The real skill: turn each directed reply into two rows, (sender, parent) and (parent, sender), with one set operation. Note that reply_to already holds the OTHER person's user ID, not a message ID, so there is nothing to look up: each reply hands you both endpoints directly. Anyone can write the UNION. The trap is reply_to IS NULL: about 70 percent of rows are original posts with no parent. Forget to filter them and the forward half emits (poster, NULL) while the reverse half maps every poster to (NULL, poster). UNION then folds all of those NULL rows into a fistful of phantom edges that exist nowhere in the data.

How to build it

Step 1: Keep only real replies

Filter to reply_to IS NOT NULL. Original posts have no parent, so they are not edges in the reply network. This filter has to live in BOTH halves of the union, not just the first: the reverse SELECT reads the same table and will leak (NULL, sender) rows if you only guard the forward one.

Step 2: Emit both directions

For every surviving reply, select the forward edge (sender_id, reply_to), then in a second SELECT over the same table select the reverse (reply_to, sender_id). Because reply_to is itself the other user's ID, two stacked SELECTs give you the mirror image without ever joining the table to itself.

Step 3: Alias and dedup

Alias the columns to user_a and user_b, and stack the two SELECTs with UNION, not UNION ALL. The expected output lists each pair once, so a pair generated twice has to collapse to a single row.

The solution

SELECT sender_id AS user_a, reply_to AS user_b
FROM chat_msgs
WHERE reply_to IS NOT NULL UNION
SELECT reply_to AS user_a, sender_id AS user_b
FROM chat_msgs
WHERE reply_to IS NOT NULL

Common follow-up questions

• When would `UNION ALL` be the right call here, and how would the row count change? (Tests understanding of implicit deduplication in `UNION` vs. `UNION ALL`.)
• What breaks if you filter `reply_to` IS NOT NULL in only the first SELECT? (Tests edge-case reasoning about the NULL filter placement.)
• If asked for the number of distinct people each user is connected to, how would you layer aggregation on top of this union? (Tests CTE or subquery wrapping to aggregate over a derived set.)