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.
Write a solution 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 result format is in the following example.
Example 1:
Input: 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 | +---------+---------+ Output: +------------------+ | recommended_page | +------------------+ | 23 | | 24 | | 56 | | 33 | | 77 | +------------------+ Explanation: User one is friend with users 2, 3, 4 and 6. Suggested pages are 23 from user 2, 24 from user 3, 56 from user 3 and 33 from user 6. Page 77 is suggested from both user 2 and user 3. Page 88 is not suggested because user 1 already likes it.
Problem Overview: You need to recommend new pages to a user based on what their friends like. A page should appear in the result if a friend liked it but the user has not already liked it.
Approach 1: Union + Equi-Join + Subquery (O(F + L) time, O(F) space)
The core idea is to first identify all friends of the target user. Since the friendship table stores relationships in two columns (user1_id, user2_id), a UNION query extracts both directions so you get a clean list of friend IDs. Next, perform an equi-join between this friend list and the Likes table to find pages those friends liked. Finally, exclude any pages already liked by the user using a subquery filter such as NOT IN. Add DISTINCT to remove duplicates when multiple friends like the same page. This approach is easy to reason about and maps directly to relational operations using database queries, joins, and subqueries.
Approach 2: Default Approach (Self-Filtering Join) (O(F + L) time, O(1) extra space)
This approach performs the recommendation in a single query pipeline. Join the Friendship table with the Likes table to map each friend to the pages they liked. A conditional filter extracts friends connected to the target user from either side of the friendship relation. Then apply a NOT IN or LEFT JOIN ... IS NULL filter against the user's existing likes to remove pages the user already follows. Use DISTINCT on page_id to avoid duplicate recommendations when multiple friends like the same page. This pattern is common in SQL interview problems where relationship tables and preference tables must be combined efficiently.
Recommended for interviews: The union-based approach is the clearest way to normalize the friendship relationship before joining with likes. Interviewers typically expect you to correctly handle the bidirectional friendship table and filter out existing likes. Showing both the union extraction and the exclusion subquery demonstrates strong understanding of relational joins and filtering logic.
First, we query all users who are friends with user_id = 1 and record them in the T table. Then, we query all pages that users in the T table like, and finally exclude the pages that user_id = 1 likes.
MySQL
MySQL
| Approach | Complexity |
|---|---|
| Union + Equi-Join + Subquery | — |
| Default Approach | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Union + Equi-Join + Subquery | O(F + L) | O(F) | When friendship relationships are stored in two columns and must be normalized before joining |
| Default Join + Filter Approach | O(F + L) | O(1) | When writing a compact SQL query that directly joins friendships with likes |
SQL Interview Insights: Best Approach to Learning Advanced SQL! - Data Science | Leetcode 1264 • Everyday Data Science • 991 views views
Watch 2 more video solutions →Practice Page Recommendations with our built-in code editor and test cases.
Practice on FleetCode