Pregunta de entrevista

Entrevista para Data Scientist

-

Meta

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.

Etiquetas:sql, datascientist
RespuestaAgregar etiquetas

Respuestas de entrevistas

40 respuestas

39

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;

Anónimo en

2

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 en

3

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 en

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 en

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 en

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;

Anónimo en

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 en

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 en

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 en

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;

Anónimo en

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 en

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 en

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;

Anónimo en

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 en

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

Anónimo en

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

Anónimo en

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 en

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;

Anónimo en

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 en

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 )

Anónimo en

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)

Anónimo en

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 en

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)

Anónimo en

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 en

0

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

Anónimo en

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

Anónimo en

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);

Anónimo en

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 en

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 en

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 en

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.

Anónimo en

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 en

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 en

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 en

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 en

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 en

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 en

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;

Anónimo en

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 en

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? en

Agregar respuestas o comentarios

Para comentar esto, Inicia sesión o regístrate.