SQL Interview Questions – Page Recommendations II

Problem Description –

You are implementing a page recommendation system for a social media website. Your system will recommended a page to user_id if the page is liked by at least one friend of user_id and is not liked by user_id.

Write an SQL query to find all the possible page recommendations for every user. Each recommendation should appear as a row in the result table with these columns:

• user_id: The ID of the user that your system is making the recommendation to.
• page_id: The ID of the page that will be recommended to user_id.
• friends_likes: The number of the friends of user_id that like page_id.

Return result table in any order.

The query result format is in the following example.

Problem Link – Page Recommendations II

Difficulty Level – Hard

Solution –

-- first, prep a table that contains all users and their friends
with t1 as (
    select user1_id as user_id, user2_id as friend_id from friendship
    union
    select user2_id as user_id, user1_id as friend_id from friendship)
    
-- then, join table
select t1.user_id, l.page_id, count(distinct t1.friend_id) as friends_likes
from t1
left join likes as l
on t1.friend_id=l.user_id

-- filter out pages that are already liked by the user
left join likes as l2
on t1.user_id=l2.user_id and l.page_id=l2.page_id
where l2.page_id is null

-- get the final output
group by t1.user_id, l.page_id