Page Recommendations II

Page Recommendations II

What is this problem about?

The Page Recommendations II problem extends the first by computing a "friendship score" for each recommended page — the number of friends who liked the page. For each user, recommend pages (not already liked) with their friendship score. This hard SQL problem adds aggregation and scoring to the recommendation query.

Why is this asked in interviews?

Meta asks this hard SQL problem to test advanced aggregation with complex JOINs. It requires computing COUNT of friends who liked each page per user, which involves a correlated multi-table join with GROUP BY and exclusion. The database interview pattern at an advanced level is demonstrated.

Algorithmic pattern used

Friend-page-count aggregation. For each user, join with Friendship to get friends, join friends with Likes, group by (user_id, page_id), count friends. Exclude pages already liked by each user. Use a CTE or subquery for the exclusion.

WITH FriendPages AS (
    SELECT CASE WHEN f.user1_id = u.user_id THEN f.user2_id ELSE f.user1_id END AS friend_id,
           u.user_id, l.page_id
    FROM Users u
    JOIN Friendship f ON u.user_id IN (f.user1_id, f.user2_id)
    JOIN Likes l ON ... -- friend's likes
    WHERE l.page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = u.user_id)
)
SELECT user_id, page_id, COUNT(*) AS friends_likes FROM FriendPages GROUP BY user_id, page_id

Common mistakes candidates make

• Using the wrong user/friend association in bidirectional friendship.
• Not excluding pages already liked by each user per user (the exclusion must be per-user).
• Missing the GROUP BY for the friendship score aggregation.
• Correlated subquery performance issues (CTE or JOIN-based exclusion is better).

Interview preparation tip

Recommendation with scoring SQL problems require three phases: (1) find connections, (2) aggregate recommended items with scores, (3) exclude already-consumed items. The per-user exclusion is the trickiest part — it must be applied within the context of each user, not globally. Practice building recommendation systems in SQL for music (liked songs of friends), movies, and news articles.