10. Page Recommendations

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

1706039149160888 Image scaled to 75%

Output

1706039165104599 Image scaled to 30%

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

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.
SELECT user2_id AS friend FROM Friendship WHERE user1_id = 1:
- Selects all user2_ids where user1_id is 1, indicating that these users are friends initiated by user 1.
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_ids where user2_id is 1, capturing friendships initiated by other users towards user 1.

Output After Step 1:


+---------+
| friend  |
+---------+
| 2       |
| 3       |
| 4       |
| 6       |
+---------+

Step 2: Find Pages Liked by These Friends

Retrieve all page_ids 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_ids from the Likes table and aliases them as recommended_page to ensure no duplicates in the recommendations.
WHERE user_id IN (SELECT friend FROM friends):
- Filters the Likes records to include only those where the user_id is in the list of friends identified in Step 1.

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.
Subquery:
- SELECT page_id FROM Likes WHERE user_id = 1:
  - Retrieves all page_ids that user 1 has already liked.
- 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_ids to avoid duplicate recommendations.

Final Output:


+-----------------+
| recommended_page|
+-----------------+
| 23              |
| 24              |
| 56              |
| 33              |
| 77              |
+-----------------+