The Blind Spot

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

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.

Pages they haven't discovered yet.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

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 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

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

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.)