Mutual Channel Connections

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

Google

Problem

Our chat platform stores one row per message, tagged with its sender and the channel it was posted to. Find the senders who have posted in both the '#engineering' and '#data-team' channels.

Two users. What channels do they share?

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

Strip the chat-app framing and this is a set intersection. You need the senders who show up in the #engineering set and in the #data-team set, computed as two independent membership checks joined by AND. The word that trips people is 'both': read it as a single channel IN ('#engineering', '#data-team') filter and you have quietly asked for everyone in either channel, a union. On this data that returns 25 senders instead of 8, because plenty of people post in one of the two rooms but never the other.

Build it in two halves

Step 1: Resolve each channel's senders separately

The senders who posted in #engineering are SELECT sender_id FROM chat_msgs WHERE channel = '#engineering', and the #data-team senders are the same query with the other literal. Each is its own set; keep them apart.

Step 2: Keep only the senders in both sets

Filter chat_msgs to rows whose sender_id sits in the first set AND in the second: sender_id IN (...) AND sender_id IN (...). The AND is the intersection. Swap it for OR, or fold the two literals into one IN list, and the meaning flips to 'either channel' and the count jumps to 25.

Step 3: Collapse repeat posters to one row

Each qualifying sender has several messages in each channel, so the raw filter returns one row per message: four rows per person here. Wrap the projection in SELECT DISTINCT sender_id so the output is the set of people, not their message volume.

The solution

SELECT DISTINCT sender_id
FROM chat_msgs
WHERE sender_id IN (SELECT sender_id FROM chat_msgs WHERE channel = '#engineering')
  AND sender_id IN (SELECT sender_id FROM chat_msgs WHERE channel = '#data-team')

Common follow-up questions

• How would you rewrite the two `IN` subqueries as `EXISTS` clauses, and when would the planner prefer one form? (Tests whether the candidate sees each membership check as a correlated existence check and knows both forms compile to a semi-join.)
• What changes if the ask becomes senders who posted in ALL of a given list of channels, not just these two? (Pushes from a fixed two-way intersection to a general 'covers every channel' pattern, usually `GROUP BY sender_id HAVING COUNT(DISTINCT channel) = N`.)
• How would you express the same intersection with a self-join on `chat_msgs`, and why might you avoid it here? (Tests the self-join alternative and awareness that it can fan out duplicates without a careful `DISTINCT`.)