SQL Interview Questions – Page Recommendation

Problem Description –

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.

Return result table in any order without duplicates.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Page Recommendation

Solution –

WITH friends_table as
(   SELECT
        users,
        friends
    FROM (
    SELECT
        user1_id as users,
        user2_id as friends
    FROM Friendship
    UNION ALL 
    SELECT 
        user2_id as users,
        user1_id as friends
    FROM Friendship
        ) x
    WHERE users = 1
)

SELECT 
    DISTINCT page_id as recommended_page
FROM Likes
WHERE user_id IN (SELECT DISTINCT friends FROM friends_table)
AND page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = 1)
ORDER BY 1