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 touser_id
.friends_likes
: The number of the friends ofuser_id
that likepage_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