Rooms in Common

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Google

Problem

We map user networks through our chat channels: two people count as connected the moment they've both posted in the same channel, no matter how many channels they end up sharing. For each sender, find how many other people they're connected to.

Two people, one channel, a connection you never counted.

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

This is a mutual-connections network problem wearing a channel-analytics costume. Two senders are connected the moment they've both posted in the same channel, and the ask is simply: how big is each sender's network? The skill being probed is joining chat_msgs to itself on channel and counting each sender's distinct partners. The trap is double-counting from two directions at once: a sender pairs with themselves on every channel they touch, and a sender who shares three channels with the same person is still ONE connection. Miss either guard and your busiest senders balloon with phantom contacts.

Building it step by step

Step 1: Self-join on channel, exclude the self-pair

Alias chat_msgs as a and b and join on a.channel = b.channel. Every matching (a, b) row means those two senders posted in the same channel. The inequality a.sender_id != b.sender_id drops the self-pair, so a sender is never counted as their own connection.

Step 2: Count each partner once

Use COUNT(DISTINCT b.sender_id) grouped by a.sender_id. The DISTINCT sits INSIDE the count and removes duplicate partners while aggregating. If a and b share three channels, or post several times in one channel, they produce many matched rows, but the same b.sender_id still counts once. This is the right place for the dedup, not a SELECT DISTINCT after the fact.

Step 3: One row per sender

GROUP BY a.sender_id gives each sender exactly one row. Every sender in chat_msgs shares a channel with someone, so the inner self-join drops nobody. The question asks for a count per sender, not a leaderboard, so there is nothing to sort. On this data every sender lands on 24: each person posts in two channels, and those two channels reach every one of the other 24 people in their circle.

The solution

SELECT a.sender_id, COUNT(DISTINCT b.sender_id) AS mutual_connections
FROM chat_msgs a
INNER JOIN chat_msgs b ON a.channel = b.channel AND a.sender_id != b.sender_id
GROUP BY a.sender_id

Redundant three-way join

Adding a third copy c joined on b.channel = c.channel AND a.sender_id = c.sender_id looks like it checks that the link runs both ways. It only repeats a constraint the first join already guarantees: a is always present in b's channel, so c is just a's own row. It returns identical numbers at triple the join cost.

Clean two-alias self-join

The first join already establishes that a and b share a channel, and that relationship is symmetric. One self-join plus the inequality and the distinct count is all the logic the metric needs.

Common follow-up questions

• Now only count two senders as connected if they share at least two different channels. How does the query change? (Tests whether the candidate can add a strength threshold, counting shared channels per pair and filtering with `HAVING`.)
• `channel` is zipf-skewed with a few very hot channels. How does that skew affect the self-join, and how would you keep the hottest channels from dominating runtime? (Tests understanding of how the self-join fans out on a skewed join key at scale.)
• Instead of the count per sender, return the list of connected sender pairs without mirrored duplicates. How would you write that? (Tests the move from a per-sender count to the underlying edge list.)