Page Recommendations: Write an SQL query to recommend pages to the user

This question was asked in Facebook interview, Ask is to give page recommendation for user1 based on Friendship and Likes.

Source: LeetCode

Difficulty: Medium

Company: Facebook

Page Recommendations

Q. Write an SQL query to recommend pages to the user with user_id = 1 using the pages that your friends liked. It should not recommend pages you already liked.

Solution 1: Using Case

SELECT DISTINCT page_id AS recommended_page
FROM (
    SELECT 
        CASE
            WHEN user1_id = 1 THEN user2_id
            WHEN user2_id = 1 THEN user1_id
        END AS user_id
    FROM Friendship
) AS a
JOIN Likes l ON a.user_id = l.user_id
WHERE page_id NOT IN (
    SELECT page_id 
    FROM Likes 
    WHERE user_id = 1
);

Solution 1: Using UNION

Select distinct (page_id) as recommended_page
from (
    Select user2_id as user_id from Friendship where user1_id = 1
    UNION
    Select user1_id as user_id from Friendship where user2_id = 1
) a
join Likes l on a.user_id = l.user_id
where page_id not in (Select page_id from Likes where user_id = 1);

Explanation:

Solution 1: Using Case

Outer Query:

• SELECT DISTINCT page_id AS recommended_page: Fetches unique page_id values that are recommended pages for user 1. The DISTINCT ensures no duplicate page_id values in the result.

Subquery (Alias a):

• This part:

SELECT 
    CASE
        WHEN user1_id = 1 THEN user2_id
        WHEN user2_id = 1 THEN user1_id
    END AS user_id
FROM Friendship;

Extracts all friends of user 1:

• If user1_id = 1, it picks user2_id (the friend).
• If user2_id = 1, it picks user1_id (the friend).
• If neither condition is true, it returns NULL.

Join with Likes Table:

• JOIN Likes l ON a.user_id = l.user_id: Links the friends (user_id) from the subquery with the Likes table, fetching all pages liked by those friends.

Filter Out Pages Already Liked by User 1:

• WHERE page_id NOT IN (...): Ensures that the recommended pages exclude any page already liked by user 1.
  • The subquery inside NOT IN fetches all page_id values from the Likes table for user 1.

Solution 1: Using UNION

This approach maintains the rest of the query as is, except for how we select the friends. In the current method, we ensure that the query does not iterate row by row through the entire table unnecessarily. Instead, it specifically targets the rows where user 1 is involved, avoiding any redundant checks.

Select user2_id as user_id from Friendship where user1_id = 1
    UNION
Select user1_id as user_id from Friendship where user2_id = 1

The UNION method further enhances efficiency by not only filtering the relevant rows but also automatically eliminating duplicates. This makes it both efficient in execution and cost-effective in terms of performance.

---

I hope this explanation was helpful to you, considering sharing it with your friends, thank you.

Read more

• Write a query to identify the employee(s) whose salary is closest to the average salary of the company.
• Display all months where sales exceeded the average monthly sales.
• Find the most common value (mode) in a specific column.
• Identify employees who have never received a performance review.
• Write a query to retrieve the first order placed by each customer.