Write a query to recommend pages to a user. A page should be recommended if it is liked by at least one friend of the user, but not already liked by the user themselves.
This social network recommendation problem is common in Snowflake interviews, testing your ability to work with graph-like data structures using SQL. Recommendation systems are at the heart of modern applications, and this question assesses whether you can translate the logic 'friends of friends' or 'what my friends like that I don't' into efficient SQL. The key challenge is coordinating multiple filtering conditions across different relationships.
Concepts tested: JOIN operations across relationship tables, subqueries with IN clause for filtering, DISTINCT to remove duplicate recommendations, understanding friend graphs in relational databases, and set difference operations (what friends like MINUS what user likes). Alternative approaches include EXCEPT or NOT EXISTS for the exclusion logic.
Snowflake customers use similar queries to: build collaborative filtering recommendation engines, generate 'people you may know' features in social networks, create content discovery systems based on peer behavior, identify cross-sell opportunities by analyzing what similar customers purchased, and power email campaigns with personalized suggestions based on network activity.
When tackling this Snowflake problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT DISTINCT fl.page_id
FROM friendship f
JOIN page_likes fl ON f.friend_id = fl.user_id
WHERE f.user_id = 1
AND fl.page_id NOT IN (
SELECT page_id FROM page_likes WHERE user_id = 1
)The NOT IN subquery excludes pages already liked by user_id=1 from the set of pages liked by their friends. It is intuitive and closely mirrors the problem statement. The subquery runs once to build the exclusion set and performs well when user_id=1 has few liked pages and page_likes is indexed on user_id. Critical warning: if the subquery ever returns a NULL value, NOT IN silently produces zero results due to SQL's three-valued logic — always verify that page_id is declared NOT NULL before relying on this pattern in production.
SELECT DISTINCT fl.page_id
FROM friendship f
JOIN page_likes fl ON f.friend_id = fl.user_id
LEFT JOIN page_likes ul
ON fl.page_id = ul.page_id
AND ul.user_id = 1
WHERE f.user_id = 1
AND ul.page_id IS NULLThe LEFT JOIN anti-join pattern matches each friend-liked page against pages already liked by user_id=1, keeping only rows where no match exists (ul.page_id IS NULL). This approach is generally more performant than NOT IN because the optimizer can use a hash or merge join rather than per-row subquery evaluation. It also handles NULLs correctly without silent failures. DISTINCT is still required since multiple friends may like the same page. This is the preferred production pattern for large friendship and page-likes graphs.
Both approaches benefit from composite indexes. Create an index on friendship(user_id, friend_id) to accelerate the initial friend lookup, and on page_likes(user_id, page_id) to speed up both the join and the exclusion check. The NOT IN subquery scans page_likes once to build the exclusion set — efficient when user_id=1 has few liked pages, but cost grows linearly with that set's size. The LEFT JOIN anti-join allows the optimizer to build a hash table of user_id=1's liked pages and probe it during the main scan, scaling more gracefully. At very large scale, materializing user_id=1's likes into a CTE before the join can improve plans in databases that re-evaluate subqueries. DISTINCT adds a deduplication step (sort or hash aggregate); enforcing a UNIQUE(user_id, page_id) constraint on page_likes eliminates duplicates at write time, potentially allowing you to drop DISTINCT. Always run EXPLAIN ANALYZE to verify the optimizer chose the expected join strategy on production data volumes.
The most critical pitfall is omitting DISTINCT — since multiple friends can like the same page, results without deduplication contain duplicate page_ids, inflating the recommendation list. The NOT IN pattern is dangerous if page_likes.page_id can be NULL: a single NULL in the subquery result causes NOT IN to return zero rows due to three-valued logic, a silent data bug with no error message. Use NOT EXISTS or LEFT JOIN/IS NULL to avoid this. Another common mistake is confusing join direction: filtering on f.friend_id = 1 instead of f.user_id = 1 returns pages liked by people who consider user 1 a friend, not user 1's own friends. Candidates also sometimes forget to scope the exclusion subquery specifically to user_id=1, accidentally excluding pages liked by anyone.
UNION the friendship table with a version where user_id and friend_id are swapped: SELECT user_id, friend_id FROM friendship UNION SELECT friend_id, user_id FROM friendship. Use this combined result as your friend source. This doubles the rows considered but ensures all mutual connections are captured regardless of which direction was inserted, making the recommendation query work correctly for any friendship storage convention.
SQL uses three-valued logic: any comparison with NULL yields UNKNOWN, not TRUE or FALSE. NOT IN checks whether a value equals none of the subquery results. When any result is NULL, the equality check produces UNKNOWN instead of FALSE, so the NOT IN condition becomes UNKNOWN and the row is excluded. A single NULL in the subquery causes the entire outer query to return zero rows — a silent, difficult-to-debug bug that NOT EXISTS or LEFT JOIN/IS NULL avoids entirely.
Add a COUNT of distinct friends per page and limit to 5: SELECT fl.page_id, COUNT(DISTINCT f.friend_id) AS friend_count FROM friendship f JOIN page_likes fl ON f.friend_id = fl.user_id LEFT JOIN page_likes ul ON fl.page_id = ul.page_id AND ul.user_id = 1 WHERE f.user_id = 1 AND ul.page_id IS NULL GROUP BY fl.page_id ORDER BY friend_count DESC LIMIT 5. This ranks pages by social proof rather than returning an unordered set.