[SQL] Write an SQL query that makes recommendations using the pages that your friends liked. Assume you have two tables: a two-column table of users and their friends, and a two-column table of users and the pages they liked. It should not recommend pages you already like.

Data Science Interview QuestionsCategory: Data Science[SQL] Write an SQL query that makes recommendations using the pages that your friends liked. Assume you have two tables: a two-column table of users and their friends, and a two-column table of users and the pages they liked. It should not recommend pages you already like.
3 Answers
MockInterview Staff answered 9 months ago

select f.userid, l.pageid
from friends f
join likes l ON l.userid = f.friendid
LEFT JOIN likes r ON (r.userid = f.userid AND r.pageid = l.pageid)
where r.pageid IS NULL;
Source

Leo Liu answered 2 months ago

What about this one?
SELECT f.user_id, l.page_id 
FROM friend f JOIN like l 
WHERE l.page_id NOT IN (SELECT page_id FROM like 
                                            WHERE user_id = f.user_id)

Leo Liu answered 2 months ago

Sorry I missed the JOIN condition. The query should be like this:
SELECT f.user_id, l.page_id 
FROM friend f JOIN like l 
ON f.friend_id = l.user_id 
WHERE l.page_id NOT IN (SELECT page_id FROM like 
                                           
                                            WHERE user_id = f.user_id)

Your Answer