Two-Way Street

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

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.

A conversation reads the same from either side.

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

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.

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