[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.
6 Answers
MockInterview Staff answered 4 years 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


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Leo Liu answered 3 years 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)


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Leo Liu answered 3 years 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)


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

nima sharif answered 3 years ago

SELECT  FROM Friend f, Likes l where f.Friend_id = l.user_id and l.page_id not in (select page_id from like where f.user_id = user_id)  


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Swapnil answered 3 years ago

create table Users (id int, friend_id int);create table likes (user_id int, page_id varchar(3)); insert into users values(1,2);insert into users values(2,3);insert into users values(1,3);insert into users values(3,2); insert into likes values(1, \’P1\’);insert into likes values(1, \’P2\’);insert into likes values(2, \’P3\’);insert into likes values(3, \’P1\’); select u1.id as uid, l1.page_id as pid from users u1 join likes l1 on u1.friend_id = l1.user_idwhere (u1.id, l1.page_id) not in (select user_id, page_id from likes); Answers 1 P33 P32 P1


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

sam answered 1 year ago

Select page_id, friend_id from ( Select page_id, friend_id From u_f a left join u_p b On a.friend_id = b.user_id ) where page_id is null


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Your Answer

15 + 5 =

Data Science Career Bootcamp