SQL Interview Question - Write a SQL Query to recommend pages to a user based on the pages his friends like.

 Lets write a SQL Query for page recommendation.

The pages are to be recommended to a user if any of his friends like that page.

If the user already likes the page, then it should not be recommended.

Problem Statement - 

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.

Practice Data -

Tables - 

User Friends and Pagelikes

DDL Script

CREATE table Pagelikes

(

userid int not null,

pageid int not null

);

CREATE table User_friends

(

userid int not null,

friendid int not null

);

Insert into Pagelikes VALUES

(1, 11), (1, 12),

(2, 11), (2, 33),

(3, 15), (3, 12),

(3, 11), (4, 44);

Insert into User_friends VALUES 

(1, 2),

(1, 3),

(1, 4),

(2, 3),

(3, 4),

(4, 2);

SQL Query -

Step 1 - Identify the friends and the pages they like

Select f.userid as UserID , l.pageid as FriendLikes

from User_friends f

join pagelikes l ON l.userid = f.friendid

Step 2 - Left Join Friend Page Likes from Step 1 above to User Page Likes

Select f.userid as User, l.pageid as FriendLikes

from User_friends f

join pagelikes l ON l.userid = f.friendid

LEFT JOIN pagelikes r ON (r.userid = f.userid AND r.pageid = l.pageid)

Step 3 - If Friend Page Like is not the same as User Page Like (Page that friend and user like are not same), then the above query (Step 2) will return NULL for User Page ID.

Select the NULL records to output the pages that are not already liked by the user.

Select UF.UserID as UserID , 

P.PageID as FriendLikes , PL.PageID as UserLikes

FROM User_Friends UF 

INNER JOIN Pagelikes P 

ON UF.FriendID = P.UserID 

LEFT JOIN Pagelikes PL 

ON UF.UserID = PL.UserID 

AND P.PageID = PL.PageID 

Where PL.PageID is NULL

Order by UF.UserID;