Page Recommendations - Problem
Imagine you're building a social media recommendation system similar to Facebook or Instagram! You have two important pieces of data:
- Friendship connections between users
- Page likes from different users
Your task is to create a smart recommendation engine that suggests pages to user_id = 1 based on what their friends have liked. The catch? Don't recommend pages they've already liked themselves!
Rules:
- Find all friends of user 1
- Collect all pages that these friends have liked
- Filter out pages that user 1 already likes
- Return unique page recommendations
This is a classic collaborative filtering problem that powers recommendation systems across the web!
Input & Output
example_1.sql — Basic Recommendation
$
Input:
Friendship:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
+----------+----------+
Likes:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 88 |
| 2 | 23 |
| 3 | 24 |
| 4 | 56 |
| 3 | 33 |
| 3 | 77 |
+---------+---------+
›
Output:
+---------+
| page_id |
+---------+
| 23 |
| 24 |
| 33 |
| 56 |
| 77 |
+---------+
💡 Note:
User 1 is friends with users 2, 3, and 4. These friends like pages [23, 24, 56, 33, 77]. User 1 already likes page 88, which is not in the friends' list, so all friend-liked pages are recommended.
example_2.sql — With Overlapping Likes
$
Input:
Friendship:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 3 | 1 |
+----------+----------+
Likes:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 11 |
| 1 | 12 |
| 2 | 11 |
| 2 | 13 |
| 3 | 12 |
| 3 | 14 |
+---------+---------+
›
Output:
+---------+
| page_id |
+---------+
| 13 |
| 14 |
+---------+
💡 Note:
User 1 is friends with users 2 and 3. Friends like pages [11, 13, 12, 14]. User 1 already likes pages 11 and 12, so only pages 13 and 14 are recommended.
example_3.sql — No Recommendations
$
Input:
Friendship:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
Likes:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 10 |
| 1 | 20 |
| 2 | 10 |
| 2 | 20 |
+---------+---------+
›
Output:
+---------+
| page_id |
+---------+
(empty result)
💡 Note:
User 1 is friends with user 2. Friend likes pages [10, 20]. User 1 already likes both pages 10 and 20, so no new recommendations are available.
Visualization
Tap to expand
Understanding the Visualization
1
Find Your Circle
Identify all friends of user 1 (bidirectional relationship)
2
Gather Preferences
Collect all pages that these friends have liked
3
Remove Duplicates
Filter out pages that user 1 has already liked
4
Generate Recommendations
Return the unique set of recommended pages
Key Takeaway
🎯 Key Insight: Use efficient set operations (UNION for friends, EXCEPT for filtering) to minimize database scans and achieve optimal O(n) performance in recommendation systems.
Time & Space Complexity
Time Complexity
O(n)
Single pass through data with efficient set operations and hash-based filtering
✓ Linear Growth
Space Complexity
O(n)
Hash sets for efficient set operations and duplicate elimination
⚡ Linearithmic Space
Constraints
- 1 ≤ user_id ≤ 104
- 1 ≤ page_id ≤ 104
- There are no duplicate rows in the Friendship table
- There are no duplicate rows in the Likes table
- Friendship is bidirectional - if (A,B) exists, then A and B are friends
- User 1 is guaranteed to exist in the database
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code