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 distinct partner once

Use COUNT(DISTINCT b.sender_id) grouped by a.sender_id. The DISTINCT lives INSIDE the count, deduplicating partners during aggregation: if a and b share three channels they produce three matched rows, but the same b.sender_id counts once. This is the correct and efficient place for the dedup, not a SELECT DISTINCT after the fact.

Step 3: Sort most-connected first, tie-break on sender_id

ORDER BY mutual_connections DESC, a.sender_id DESC puts the most-connected senders first. Because many senders tie on the same connection count, the count alone is not a total order, so the second key a.sender_id DESC breaks ties deterministically and makes the row order a function of the input. Without it two correct answers could disagree on row order.

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 verifies the link is bidirectional, but it re-asserts 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; that relationship is inherently 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 extend the connection definition with a strength threshold using HAVING or a channel-count aggregate.)
• `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, high-cardinality join key at scale.)
• Instead of the count per sender, return the distinct list of connected sender pairs without mirrored duplicates. How would you write that? (Tests ability to move from a per-sender count to the underlying edge list.)