0% completed
Problem
Table: Friendship
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
(user1_id, user2_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that there is a friendship relation between user1_id and user2_id.
Table: Likes
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| page_id | int |
+-------------+---------+
(user_id, page_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that user_id likes page_id.
Problem Definition
Write a solution to recommend pages to the user with user_id = 1
using the pages that his/her friends liked. It should not recommend pages user_1 already liked.
Return result table in any order without duplicates.
Example
Output
Try It Yourself
Solution
To recommend pages to the user with user_id = 1
based on the pages liked by their friends, while excluding the pages that user 1 has already liked, we can follow a systematic approach. This solution leverages SQL's WITH
clause (Common Table Expressions), UNION ALL
, and subqueries to efficiently identify the relevant pages for recommendation.
- Identify All Friends of User 1: Retrieve all users who are friends with user 1 by examining both directions of the friendship relation.
- Find Pages Liked by These Friends: Extract all pages liked by the identified friends.
- Exclude Pages Already Liked by User 1: Ensure that the recommended pages do not include any pages that user 1 has already liked.
- Select Distinct Recommended Pages: Remove any duplicate page entries to provide a clean list of recommendations.
SQL Query
WITH friends AS ( (SELECT user2_id AS friend FROM Friendship WHERE user1_id = 1) UNION ALL (SELECT user1_id AS friend FROM Friendship WHERE user2_id = 1) ) SELECT DISTINCT page_id AS recommended_page FROM Likes WHERE user_id IN (SELECT friend FROM friends) AND page_id NOT IN ( SELECT page_id FROM Likes WHERE user_id = 1 );
Step-by-Step Approach
Step 1: Identify All Friends of User 1
Retrieve all user IDs that are friends with user 1 by checking both user1_id
and user2_id
in the Friendship
table.
SQL Query:
WITH friends AS ( (SELECT user2_id AS friend FROM Friendship WHERE user1_id = 1) UNION ALL (SELECT user1_id AS friend FROM Friendship WHERE user2_id = 1) )
Explanation:
WITH friends AS (...)
:- Defines a Common Table Expression (CTE) named
friends
to store the list of user IDs who are friends with user 1.
- Defines a Common Table Expression (CTE) named
SELECT user2_id AS friend FROM Friendship WHERE user1_id = 1
:- Selects all
user2_id
s whereuser1_id
is 1, indicating that these users are friends initiated by user 1.
- Selects all
UNION ALL
:- Combines the results from both SELECT statements, ensuring that all friends are included without eliminating duplicates (though duplicates are unlikely in this context).
SELECT user1_id AS friend FROM Friendship WHERE user2_id = 1
:- Selects all
user1_id
s whereuser2_id
is 1, capturing friendships initiated by other users towards user 1.
- Selects all
Output After Step 1:
+---------+ | friend | +---------+ | 2 | | 3 | | 4 | | 6 | +---------+
Step 2: Find Pages Liked by These Friends
Retrieve all page_id
s that have been liked by the identified friends.
SQL Query:
SELECT DISTINCT page_id AS recommended_page FROM Likes WHERE user_id IN (SELECT friend FROM friends)
Explanation:
SELECT DISTINCT page_id AS recommended_page
:- Selects unique
page_id
s from theLikes
table and aliases them asrecommended_page
to ensure no duplicates in the recommendations.
- Selects unique
WHERE user_id IN (SELECT friend FROM friends)
:- Filters the
Likes
records to include only those where theuser_id
is in the list of friends identified in Step 1.
- Filters the
Output After Step 2:
+-----------------+ | recommended_page| +-----------------+ | 23 | | 24 | | 56 | | 33 | | 77 | | 88 | +-----------------+
Step 3: Exclude Pages Already Liked by User 1
Ensure that the recommended pages do not include any pages that user 1 has already liked.
SQL Query:
AND page_id NOT IN ( SELECT page_id FROM Likes WHERE user_id = 1 )
Explanation:
AND page_id NOT IN (...)
:- Further filters the recommended pages by excluding any
page_id
that appears in the subquery.
- Further filters the recommended pages by excluding any
- Subquery:
SELECT page_id FROM Likes WHERE user_id = 1
:- Retrieves all
page_id
s that user 1 has already liked.
- Retrieves all
NOT IN
:- Ensures that none of the pages already liked by user 1 are included in the final recommendations.
Output After Step 3:
+-----------------+ | recommended_page| +-----------------+ | 23 | | 24 | | 56 | | 33 | | 77 | +-----------------+
Step 4: Select Distinct Recommended Pages
Ensure that each recommended page appears only once in the final output, removing any duplicates that may arise from multiple friends liking the same page.
SQL Query:
SELECT DISTINCT page_id AS recommended_page FROM Likes WHERE user_id IN (...);
Explanation:
SELECT DISTINCT page_id AS recommended_page
:- Selects unique
page_id
s to avoid duplicate recommendations.
- Selects unique
Final Output:
+-----------------+ | recommended_page| +-----------------+ | 23 | | 24 | | 56 | | 33 | | 77 | +-----------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible