# Rooms in Common > Two people, one channel, a connection you never counted. Canonical URL: 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. ## Tables ### chat_msgs - msg_id INTEGER - channel TEXT - sender_id INTEGER - content TEXT - msg_type TEXT - sent_at TEXT - edited INTEGER - reply_to INTEGER Sample rows (10 of 200): | msg_id | channel | sender_id | content | msg_type | sent_at | edited | reply_to | | --- | --- | --- | --- | --- | --- | --- | --- | | 147 | #engineering | 197 | Anyone seeing increased latency? | file | 2026-02-02 01:07:00 | 0 | NULL | | 194 | #incidents | 294 | PR #412 ready for review | reaction | 2026-03-03 02:14:00 | 0 | NULL | | 241 | #random | 391 | Dashboard is down | thread_reply | 2026-04-04 03:21:00 | 0 | NULL | | 288 | #deployments | 488 | Fixed the flaky test | system | 2026-05-05 04:28:00 | 0 | NULL | | 335 | #data-team | 585 | Merged the migration | Text | 2026-06-06 05:35:00 | 0 | NULL | | 382 | #general | 682 | NULL | FILE | 2026-07-07 06:42:00 | 0 | NULL | | 429 | #engineering | 779 | LGTM | text | 2026-08-08 07:49:00 | 0 | NULL | | 476 | #incidents | 876 | 👍 | file | 2026-09-09 08:56:00 | 0 | 5 | | 523 | #random | 973 | | reaction | 2026-01-10 09:03:00 | 0 | NULL | | 570 | #deployments | 1070 | Can someone check the pipeline? | thread_reply | 2026-02-11 10:10:00 | 0 | NULL | ## Expected output First 5 of 50 rows: | sender_id | mutual_connections | | --- | --- | | 100 | 24 | | 197 | 24 | | 294 | 24 | | 391 | 24 | | 488 | 24 | ## 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. > **One self-join, two guards** > > The whole problem is one self-join plus two guards. Alias `chat_msgs` twice, join the copies on the same `channel`, exclude the self-pair with `a.sender_id != b.sender_id`, then wrap the partner in `COUNT(DISTINCT ...)` so repeated channels and repeated messages between the same two people collapse to one. --- ### 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 **Distinct channel-sharing partners per sender** ```sql 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. > **Hot channels dominate the join** > > At ~50M rows the join fans out on `channel` (cardinality ~300K, zipf-skewed), so a few hot channels dominate the work: a channel with n messages yields about n squared pairs. A composite index on `(channel, sender_id)` lets the self-join read ordered keys. Projecting only `channel` and `sender_id`, and deduplicating them before the join, shrinks each side to one row per person per channel. > **Trusting the symmetry is the senior tell** > > Whether you exclude the self-pair, whether you count distinct PARTNERS rather than raw matched rows, and whether you recognize that a symmetric relationship needs only one self-join. Adding extra joins to prove the link runs both ways signals that the candidate does not trust the symmetry. > **Both guards change the number** > > Dropping the `a.sender_id != b.sender_id` guard counts every sender as connected to themselves, adding a phantom to each network. Using `COUNT(b.sender_id)` without `DISTINCT` counts one row per pair of messages, so a sender with four messages across two busy channels is overcounted many times over. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/rooms_in_common) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.