LeetCode 1264. Page Recommendations SQL Solution

Problem

LeetCode SQL Problem

1264. Page Recommendations

Friendship table

user1_id	user2_id
1	2
1	3
1	4
2	3
2	4
2	5
6	1

Likes table

user_id	page_id
1	88
2	23
3	24
4	56
5	11
6	33
2	77
3	77
6	88

Solution - LEFT JOIN + Subquery

Perform LEFT JOIN twice to recommend pages each user likes to his/her friends.

We're only interested in pages recommended to user 1 by his/her friends so we added WHERE clause to achieve this.

We also need to exclude recommended pages user 1 already liked. This is achieved with a subquery.

MySQL

Page Recommendations

SELECT DISTINCT L.page_id AS recommended_page
FROM Likes AS L
LEFT JOIN Friendship AS F1 ON L.user_id = F1.user1_id
LEFT JOIN Friendship AS F2 ON L.user_id = F2.user2_id
WHERE (
		F1.user2_id = 1
		OR F2.user1_id = 1
		) -- Pages recommended to user 1 by friends
	AND L.page_id NOT IN (
		SELECT page_id
		FROM Likes
		WHERE user_id = 1
		) -- Exclude pages user 1 already liked

TSQL

Page Recommendations

SELECT DISTINCT L.page_id AS recommended_page
FROM Likes AS L
LEFT JOIN Friendship AS F1 ON L.user_id = F1.user1_id
LEFT JOIN Friendship AS F2 ON L.user_id = F2.user2_id
WHERE (
		F1.user2_id = 1
		OR F2.user1_id = 1
		) -- Pages recommended to user 1 by friends
	AND L.page_id NOT IN (
		SELECT page_id
		FROM Likes
		WHERE user_id = 1
		) -- Exclude pages user 1 already liked

Query Output

recommended_page
23
24
33
56
77

Solution - CTE + Subquery

To find friends of the user with user_id = 1, use WHERE clause and IF function in table Friendship and store temporary results in a common table expression.

Then select DISTINCT pages that the friends liked, but remove pages that user 1 already liked (using a subquery).

MySQL

Page Recommendations

-- Get friends of user 1
WITH FriendsOfUserX
AS (
	SELECT if(F.user1_id = 1, F.user2_id, F.user1_id) AS user_id
	FROM Friendship AS F
	WHERE F.user1_id = 1
		OR F.user2_id = 1
	)
-- Get the pages user 1's friends liked.
SELECT DISTINCT page_id AS recommended_page
FROM Likes AS L
WHERE L.user_id IN (
		SELECT user_id
		FROM FriendsOfUserX
		)
	-- It should not recommend pages user 1 already liked
	AND L.page_id NOT IN (
		SELECT page_id
		FROM Likes
		WHERE user_id = 1
		)

TSQL

Page Recommendations

-- Get friends of user 1
WITH FriendsOfUserX
AS (
	SELECT if(F.user1_id = 1, F.user2_id, F.user1_id) AS user_id
	FROM Friendship AS F
	WHERE F.user1_id = 1
		OR F.user2_id = 1
	)
-- Get the pages user 1's friends liked.
SELECT DISTINCT page_id AS recommended_page
FROM Likes AS L
WHERE L.user_id IN (
		SELECT user_id
		FROM FriendsOfUserX
		)
	-- It should not recommend pages user 1 already liked
	AND L.page_id NOT IN (
		SELECT page_id
		FROM Likes
		WHERE user_id = 1
		)

Query Output

recommended_page
23
24
33
56
77

Solution - CTE + Union + Subquery

To find friends of the user with user_id = 1, use user2_id union all user1_id in table Friendship and store temporary results in a common table expression.

Then select DISTINCT pages that the friends liked, but remove pages that user 1 already liked.

MySQL

Page Recommendations

-- Get friends of user 1
WITH FriendsOfUserX
AS (
	SELECT F.user2_id AS user_id
	FROM Friendship AS F
	WHERE F.user1_id = 1

	UNION

	SELECT F.user1_id
	FROM Friendship AS F
	WHERE F.user2_id = 1
	)
-- Get the pages user 1's friends liked.
SELECT DISTINCT page_id AS recommended_page
FROM Likes AS L
WHERE L.user_id IN (
		SELECT user_id
		FROM FriendsOfUserX
		)
	-- It should not recommend pages user 1 already liked
	AND L.page_id NOT IN (
		SELECT page_id
		FROM Likes
		WHERE user_id = 1
		)

TSQL

Page Recommendations

-- Get friends of user 1
WITH FriendsOfUserX
AS (
	SELECT F.user2_id AS user_id
	FROM Friendship AS F
	WHERE F.user1_id = 1

	UNION

	SELECT F.user1_id
	FROM Friendship AS F
	WHERE F.user2_id = 1
	)
-- Get the pages user 1's friends liked.
SELECT DISTINCT page_id AS recommended_page
FROM Likes AS L
WHERE L.user_id IN (
		SELECT user_id
		FROM FriendsOfUserX
		)
	-- It should not recommend pages user 1 already liked
	AND L.page_id NOT IN (
		SELECT page_id
		FROM Likes
		WHERE user_id = 1
		)

Query Output

recommended_page
23
24
33
56
77