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.

Question

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.

Utilisateur anonyme · Accepted Answer

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;

Is this right? · Answer

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

Zach · Answer

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

 · Answer

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;

John · Answer

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?

anonymous · Answer

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

CorrelatedSubqueriesAreGross · Answer

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;

vamshi · Answer

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

SAR662 · Answer

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.

Harshit · Answer

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
  )

Justin · Answer

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?

 · Answer

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;

Harshit · Answer

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
  )

Taking a stab at it · Answer

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

friends recommendation · Answer

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
;

 · Answer

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;

Praty · Answer

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;

 · Answer

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

 · Answer

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 · Answer

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)

 · Answer

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 · Answer

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;

 · Answer

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
	)

 · Answer

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)

Efrat · Answer

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)

 · Answer

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 Aspirant · Answer

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

 · Answer

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

 · Answer

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

 · Answer

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

Hussam · Answer

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 · Answer

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

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

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

 · Answer

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.

Tapan · Answer

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

Senthil Kumar Shanmugha Sundaram · Answer

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 · Answer

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)

sujathha · Answer

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;

sqlfiddler · Answer

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
)

 · Answer

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

Questions d'entretien

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.

Réponses aux questions d'entretien

Ajouter des réponses ou des commentaires

Carrières chez Meta