# The Blind Spot > Pages they haven't discovered yet. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 · Asked in: Meta ## Problem We run a content platform with a built-in team chat, and we want to recommend pages a person has not opened yet. Treat two people as connected when they have both posted in the same chat channel, and for each person surface the pages that at least two different connections have viewed but the person themselves never has. ## Tables ### page_views - view_id INTEGER - page_url TEXT - user_id INTEGER - referrer TEXT - dur_ms INTEGER - device TEXT - viewed_at TEXT Sample rows (10 of 200): | view_id | page_url | user_id | referrer | dur_ms | device | viewed_at | | --- | --- | --- | --- | --- | --- | --- | | 5053 | /products | 1555 | amazon.com | 637 | desktop | 2026-09-02 01:09:00 | | 5106 | /checkout | 1652 | twitter.com | 774 | tablet | 2026-09-03 02:18:00 | | 5159 | /profile | 1749 | github.com | 911 | Mobile | 2026-09-04 03:27:00 | | 5212 | /search | 1846 | NULL | 1048 | DESKTOP | 2026-09-05 04:36:00 | | 5265 | /about | 1943 | reddit.com | 1185 | mobile | 2026-09-06 05:45:00 | | 5318 | /blog/intro | 1555 | NULL | NULL | desktop | 2026-09-07 06:54:00 | | 5371 | /pricing | 1652 | bing.com | 1459 | tablet | 2026-09-08 07:03:00 | | 5424 | /docs/api | 1749 | news.ycombinator.com | 1596 | Mobile | 2026-09-09 08:12:00 | | 5477 | /settings | 1846 | NULL | 1733 | DESKTOP | 2026-09-10 09:21:00 | | 5530 | /Home | 1943 | bing.com | 1870 | mobile | 2026-09-11 10:30:00 | ### 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 499 rows: | user_id | content_id | | --- | --- | | 197 | /Home | | 197 | /checkout | | 197 | /search | | 197 | /about | | 197 | /docs/api | ## Worked solution and explanation ### What this problem really is This is a per-person set difference with a popularity gate, wearing a social-graph costume. The real question: take every page a person's chat connections have viewed, keep the ones at least two DIFFERENT connections saw, and subtract everything the person has already opened. Anyone can self-join `chat_msgs` to find who shares a channel. The hard part is proving 'two different connections' with no aggregate in the toolbox: judge popularity by counting connection-view rows and one connection who shares two channels with you, or who viewed the page five times, clears the bar alone. Then there is the exclusion: key it on `user_id` alone instead of the (`user_id`, `content_id`) pair and you delete pages other people viewed, not the ones this person did. --- ### Break down the requirements #### Step 1: Find each person's connections and what they viewed `connection_views` self-joins `chat_msgs` to itself on the same `channel` where the two `sender_id` values differ, so a person is never paired with themselves, then joins to `page_views` on the connection's `sender_id`. Each row is (person, one of their connections, a page that connection viewed). Leave the rows undeduped here: the next step needs the full evidence. #### Step 2: Prove two DIFFERENT connections, without counting `popular` self-joins `connection_views` to itself on the same (`user_id`, `content_id`) where `connection_id` differs. A matching pair can only exist when two genuinely different connections both viewed that page for that person, so a single connection, no matter how many channels they share or how often they viewed, can never pair with themselves. `SELECT DISTINCT` then collapses the many surviving pairs down to one (person, page) row. #### Step 3: Subtract the person's own views LEFT JOIN `popular` back to `page_views` on BOTH `user_id` and `page_url`, then keep only the rows where the match is NULL. That NULL means this exact person never viewed this exact page, so it is a genuine recommendation. --- ### The solution **Recommendations from connections' viewing history** ```sql WITH connection_views AS ( SELECT cm1.sender_id AS user_id, cm2.sender_id AS connection_id, pv.page_url AS content_id FROM chat_msgs cm1 INNER JOIN chat_msgs cm2 ON cm1.channel = cm2.channel AND cm1.sender_id <> cm2.sender_id INNER JOIN page_views pv ON pv.user_id = cm2.sender_id ), popular AS ( SELECT DISTINCT cv1.user_id, cv1.content_id FROM connection_views cv1 INNER JOIN connection_views cv2 ON cv1.user_id = cv2.user_id AND cv1.content_id = cv2.content_id AND cv1.connection_id <> cv2.connection_id ) SELECT p.user_id, p.content_id FROM popular p LEFT JOIN page_views own ON own.user_id = p.user_id AND own.page_url = p.content_id WHERE own.page_url IS NULL ``` > **Two different people, proven by a join not a count** > > The inequality self-join does the work an aggregate usually does. Because a pair needs `connection_id` values that differ, one connection can never satisfy 'two different connections' by viewing a page repeatedly or by sharing several channels with you. Drop the `<>` and every row pairs with itself, so a page one single connection saw would qualify. `SELECT DISTINCT` is then load-bearing: without it every ordered pair of connections becomes its own output row. > **The exclusion must be keyed on the pair** > > If you write the exclusion as `WHERE content_id NOT IN (SELECT page_url FROM page_views)`, you drop every page anyone viewed, not just this person, and recommend almost nothing. Keying the LEFT JOIN on (`user_id`, `content_id`) together is what scopes 'never viewed' to the same person. > **Nail the definitions first** > > Whether the candidate pins down what a connection is, and how strong the signal must be, before writing SQL. Co-participation in a channel is the simplest workable definition; the two-connection bar is a design choice worth confirming; and `reply_to` could tighten the relationship into a directed reply edge. Strong candidates ask about all three rather than assuming. > **Where the cost hides** > > The self-join on `chat_msgs` (60M rows) explodes on hot channels: a channel with k posters yields k*(k-1) connection pairs before anything collapses, and each pair then fans out across the connection's `page_views` (500M rows). The second self-join on `connection_views` is heavier still. In production you would pre-materialize the connection graph and pre-aggregate views per (person, page) rather than re-derive both live. --- ## Common follow-up questions - How would you rank each person's recommendations by how many connections viewed the page? _(Introduces an aggregate: count the DISTINCT connections per (person, page) and sort descending, a natural extension of the inequality self-join.)_ - How would you keep only the top 5 recommendations per person? _(Tests `ROW_NUMBER` partitioned by user with a rank filter after the anti-join.)_ - How would this hold up with 100M people and daily refreshes? _(Discusses materialized views, incremental refresh, and moving the connection graph to a graph store.)_ - What changes if connections are only people who directly replied to each other? _(Tests using `reply_to` to build a directed reply edge instead of channel co-participation.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_blind_spot) - [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.