Interview Question

Data Scientist Interview

-

Facebook

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.

Tags:sql, datascientist
AnswerAdd Tags

Interview Answers

40 Answers

38

CREATE temporary table likes ( userid int not null, pageid int not null ) CREATE temporary table friends ( userid int not null, friendid int not null ) insert into likes VALUES (1, 101), (1, 201), (2, 201), (2, 301); insert into friends VALUES (1, 2); 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;

Anonymous on

2

friends (userid, friendid) pages (userid, pages) select a.userid, b.pageid from friends a, pages b where a.friendid = b.userid group by a.userid, b.pageid except select * from pages;

CorrelatedSubqueriesAreGross on

3

Use Except select f.user_id, l.page_id from friends f inner join likes l on f.fd_id = l.user_id group by f.user_id, l.page_id -- for each user, the unique pages that liked by their friends Except select user_id, page_id from likes

Zach on

3

user_friend(user_id,friend_user_id) user_page(user_id,page_id) select uf.user_id,up.page_id from user_friend as uf, user_page as up where uf.friend_user_id = up.user_id and uf.user_id != up.user_id;

Anonymous on

2

SELECT f.userid, l.pageid FROM friends f LEFT JOIN likes l ON f.friendid = l.userid WHERE l.pageid NOT IN (SELECT r.pageid FROM likes r WHERE f.userid = r.userid) Can someone tell me if this works?

John on

1

Given two tables: Friends ("userA", "userB) -- no defined direction of friendship Likes ("user", "page") Here are the likes for one of the users ("bob"). SELECT page FROM likes WHERE USER IN (SELECT userA FROM friends WHERE userB="bob" UNION SELECT userB FROM friends WHERE userA="bob") EXCEPT SELECT page FROM likes WHERE USER = "bob"; This is in SQLite. If it were in any other SQL, I'd make this a stored procedure and then run a table of the distinct users through the procedure.

SAR662 on

1

select w.userid, w.pageid from ( select f.userid, l.pageid from rollups_new.friends f join rollups_new.likes l ON l.userid = f.friendid) w left join rollups_new.likes l on w.userid=l.userid and w.pageid=l.pageid where l.pageid is null

anonymous on

0

# Postgres WITH pages AS ( SELECT u.liked_page, COUNT(*) FROM friends f JOIN user_pages u ON f.friend = u.this_user WHERE f.this_user = 1 AND u.liked_page NOT IN (SELECT liked_page FROM user_pages WHERE this_user = 1) GROUP BY u.liked_page ) SELECT p.liked_page AS page FROM pages p ORDER BY COUNT DESC;

Anonymous on

0

create table friends(u_id number,fid number); create table page(u_id number ,p_id number) SELECT f.u_id, f_page.p_id FROM friends f, page f_page WHERE f.fid = f_page.u_id AND f_page.p_id NOT IN (SELECT p_id FROM page my_page WHERE my_page.u_id = f.u_id ) UNION SELECT f.u_id, f_page.p_id FROM (SELECT fid AS u_id , u_id AS fid FROM friends ) f, page f_page WHERE f.fid = f_page.u_id AND f_page.p_id NOT IN (SELECT p_id FROM page my_page WHERE my_page.u_id = f.u_id )

Harshit on

0

Schema: friendship (user_1, user_2) # assuming that user 1 made the friend request, but I never know if it's me or the friend) likes (user_id, page_id) select page_id, count(*) as cnt from likes as a inner join ( # list of (unique) user_ids that I'm friends with select case when user_1 is null then user_2 else user_1 end as user_id from friendship where 1=1 and (user_1 = "my_user_id" OR user_2 = "my_user_id") group by 1 ) as b on a.user_id = b.user_id and user_id != "my_user_id" # pages I did not already like group by page_id order by cnt desc

Taking a stab at it on

0

user_friend(user_id,friend_user_id) user_page(user_id,page_id) select uf.user_id,up.page_id from user_friend as uf, user_page as up where uf.user_id up.user_id;

Anonymous on

0

Select b.users, b.pages from pages_table b Where b.users in ( Select a.friends from friends_table a Where a.users = my_id ) and b.pages not in ( select c.pages from pages_table c where c.users = my_id ) How about this one?

Justin on

0

SELECT a.user_id, b.page_id FROM a JOIN b ON friend_id = b.user_id LEFT JOIN b b2 ON b.page_id = b2.page_id AND b.user_id = b2.user_id WHERE b2.page_id IS NULL ;

friends recommendation on

0

select f.user_id, p.page_id, count(*) as total from friends f left join pages p on f.friend_id = p.user_id group by f.user_id, p.page_id order by f.user_id, total desc;

Anonymous on

0

this is the easiest one I could come up with- select a.userid as uid, a.friendid as fid, b.pageid as pid from friends a right join likes b on a.friendid = b.userid where a.userid is not NULL;

Praty on

0

select userid, pageid , sum(ccc) from ( select f.userid, l.pageid , count(l.pageid) ccc --into t1111 from friends f join likes l ON l.userid = f.friendid group by f.userid, l.pageid union all select userid ,pageid , -1 from likes )a group by userid, pageid having sum(ccc) > 0

Anonymous on

0

friend_table (users, friends) page_table (users, pages) select f.users, p.pages from (select * from friend_table f left join page_table p on f.friends = p. users left join page_table p2 on f.users = p2.users) where p.pages != p2.pages

Anonymous on

0

friends: friend1, friend2 likes: userid, page want a table that has: userid, page (for each user, what are the pages that fb should recommend to that friend based on the pages that his friend liked; if there is no recommendatios to be made for this person then he doesn't appear in the final table) SELECT friend1 AS userid, page FROM (SELECT * FROM (SELECT friend1 AS friend2, friend2 AS friend1 FROM friends UNION SELECT * FROM friends) T1 GROUP BY friend1, friend2) T2 INNER JOIN likes L ON T2.friend2 = L.userid WHERE NOT EXIST (SELECT * FROM likes L1 WHERE T2.friend1 = L1.userid AND T2.page = L1.page)

Anonymous on

0

FROM ( // one row represent connection. to deflate and bring all users to userId column select userId, friendId from Friend union all select friendId as userId, userId as friendId from Friend where friendId is not null ) f left join postLike pl on f.friendId = pl.userId WHERE // filter out pages liked by user from entire list of pages liked by user and/or his friend (f.userID, pl.post_id) not in ( select f.userID, pl.post_id FROM ( select userId, friendId from Friend union all select friendId as userId, userId as friendId from Friend where friendId is not null ) f left join postLike pl on f.userId = pl.userId ) GROUP BY f.userID, pl.post_id;

Anonymous on

0

select f.userID, pl.post_id, count(*), rank () over (partition by f.userID order by count(*) desc) FROM ( // one row represent connection. to deflate and bring all users to userId column select userId, friendId from Friend union all select friendId as userId, userId as friendId from Friend where friendId is not null ) f left join postLike pl on f.friendId = pl.userId WHERE // filter out pages liked by user from entire list of pages liked by user and/or his friend (f.userID, pl.post_id) not in ( select f.userID, pl.post_id FROM ( select userId, friendId from Friend union all select friendId as userId, userId as friendId from Friend where friendId is not null ) f left join postLike pl on f.userId = pl.userId ) GROUP BY f.userID, pl.post_id;

Trisha on

0

select distinct a.* from ( select t1.userid, t2.pageid from friends t1 join likes t2 on t1.friendid=t2.userid order by 1,2 )a where a.pageid not in ( select pageid from likes t3 where t3.userid=a.userid )

Anonymous on

0

friends table: user_id| friend|id page table: user_id| post_id select f.user_id, p.post_id from friends f join page p on f.friend_id=p.user_id where p.post_id not in (select p.post_id from friends f join pages p on f.user_id=p.user_id)

Anonymous on

0

select f1.userid, l1.pageid as reccomendation from friends f1 left join likes l1 on f1.friendid = l1.id where l1.pageid not in(select pageid from likes where likes.id = f1.userid) union all select f2.friendid as userid, l2.pageid as reccomendation from friends f2 left join likes l2 on f2.userid = l2.id where l2.pageid not in(select pageid from likes where likes.id = f2.friendid)

Efrat on

0

SELECT f.UserID, l.PageID FROM Friends f, Likes l WHERE f.FrndID = l.UserID AND l.PageID NOT IN (SELECT PageID from Likes WHERE UserID = f.UserID)

Anonymous on

0

select likes.pageid from (select * from friends) friends , (select * from likes) likes where friends.friendid=likes.userid and likes.pageid not in(select pageid from likes where likes.userid=friends.userid) ;

Anonymous Aspirant on

0

select F.userid, L.pageid from likes L inner join friends F on L.userid = F.friendid minus select userid, pageid from likes;

Anonymous on

0

select distinct friendid, userpage from ( select a.userid, friendid, l.pageid as userpage, m.pageid as friendpage from ((select userid , friendid from friends) union all (select friendid as userid, userid as friendid from friends) ) a left join likes l on a.userid = l.userid left join likes m on m.userid = a.friendid and l.pageid = m.pageid ) as b where friendpage is null order by friendid

Anonymous on

0

CREATE table likes ( userid int not null, pageid int not null ) CREATE table friends ( userid int not null, friendid int not null ) insert into likes VALUES (1, 101), (1, 201), (2, 201), (2, 301), (3, 401), (3, 101), (4, 201); insert into friends VALUES (1, 2), (2, 3), (3, 4);

Anonymous on

0

Select F.userID, P.pagesLiked, Count(P.friend) as numofLikingFriends From userFriend F, userPage P Where F.friend=P.userID AND F.userID NOT IN (Select P2.userID FROM userPage P2 WHERE P2.userID F.userID) Goupby F.userID, P.pagesLiked Having numofLikingFriends > 10

Hussam on

0

Correcting: Select F.userID, P.pagesLiked, Count(P.friend) as numofLikingFriends From userFriend F JOIN userPage P ON F.friend=P.userID Where F.userID NOT IN (Select P2.userID FROM userPage P2 WHERE P2.userID = F.userID AND P.pageID = P2.PageID) Goupby F.userID, P.pagesLiked Having numofLikingFriends > 10

Hussam on

0

Select distinct f.user1_id, l.page_id from friendship f JOIN likes l on f.user2_id=l.user_id where l.page_id not in (select page_id from likes where user_id=f.user1_id) union Select distinct f.user2_id, l.page_id from friendship f JOIN likes l on f.user1_id=l.user_id where l.page_id not in (select page_id from likes where user_id=f.user2_id) order by user1_id asc,page_id asc

Simple Join + Union Statement to get n:m relationships for page recommendations on

0

is userid in each table unique ? or can do we have in friends for example (1,2),(1,4),(1,6) meaning that user 1 is a friend of 2,4,6 ? same goes for likes table.

Anonymous on

0

I know subqueries might be discouraged but here goes. select f.*, l.pid from friends f join likes l on f.fid = l.userid where l.pid not in (select l.pid from likes l where l.userid= f.userid ) order by f.userid

Tapan on

0

SELECT f.userid, l.pageid FROM friends f LEFT JOIN likes l ON f.friendid = l.userid WHERE l.pageid NOT IN (SELECT r.pageid FROM likes r WHERE f.userid = r.userid) union SELECT f.friendid userid, l.pageid FROM friends f LEFT JOIN likes l ON f.userid = l.userid WHERE l.pageid NOT IN (SELECT r.pageid FROM likes r WHERE f.userid != r.userid)

Senthil Kumar Shanmugha Sundaram on

0

SELECT f.userid, l.pageid FROM friends f LEFT JOIN likes l ON f.friendid = l.userid WHERE l.pageid NOT IN (SELECT r.pageid FROM likes r WHERE f.userid = r.userid) union SELECT f.friendid userid, l.pageid FROM friends f LEFT JOIN likes l ON f.userid = l.userid WHERE l.pageid NOT IN (SELECT r.pageid FROM likes r WHERE f.friendid= r.userid)

Senthil Kumar Shanmugha sundaram on

0

select u.userid, p.movieid, count(p.movieid) FROM usersf AS u INNER JOIN movies_watched AS p ON u.friendid = p.userid where u.userid not in ( select userid from movies_Watched where movieid = p.movieid) GROUP BY u.userid, p.movieid ORDER BY u.userid, count(p.movieid) desc, p.movieid;

sujathha on

0

CREATE table likes ( userid int not null, pageid int not null ); CREATE table friends ( userid int not null, friendid int not null ); insert into likes VALUES (1, 101), (1, 201), (2, 201), (2, 301); insert into friends VALUES (1, 2); select pageid from friends a join likes b where a.friendid = b.userid and pageid not in ( select pageid from friends x join likes y on x.userid = y.userid where x.userid = 1 )

sqlfiddler on

2

select a.user_id,b.page_id from friends a cross join ( select page_id,count(*) from page where a.friend_id = page.user_id and page.user_id a.user_id group by 1 order by count desc ) b

vamshi on

1

SELECT f.u_id, f_page.p_id FROM friends f, page f_page WHERE f.fid = f_page.u_id AND f_page.p_id NOT IN (SELECT p_id FROM page my_page WHERE my_page.u_id = f.u_id ) UNION SELECT f.u_id, f_page.p_id FROM (SELECT fid AS u_id , u_id AS fid FROM friends ) f, page f_page WHERE f.fid = f_page.u_id AND f_page.p_id NOT IN (SELECT p_id FROM page my_page WHERE my_page.u_id = f.u_id )

Harshit on

7

Am I underthinking this? I came up with the following but it seems crazy simpler than everyone elses. Am I missing something? Table 1 - Friends (User_id, Friend_id) Table 2 - Likes (User_id, page_id) Select f.user_id, page_id from friends f join likes l on f.friend_id=l.user_id where f.user_id != l.user_id

Is this right? on

Add Answers or Comments

To comment on this, Sign In or Sign Up.