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
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
friends_likes: The number of the friends of
Return result table in any order.
The query result format is in the following example.
Problem Link – Page Recommendations II
Difficulty Level – Hard
-- 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