Page Recommendations - Problem
You have two tables: Friendship and Likes. The Friendship table represents friendship relationships between users, and the Likes table represents which pages each user likes.
Write a SQL query to recommend pages to the user with user_id = 1 using the pages that their friends liked. The recommendations should:
- Include only pages that friends of user 1 have liked
- Exclude pages that user 1 already likes
- Return results without duplicates
Return the result table in any order.
Table Schema
Friendship
| Column Name | Type | Description |
|---|---|---|
user1_id
PK
|
int | First user in friendship |
user2_id
PK
|
int | Second user in friendship |
Primary Key: (user1_id, user2_id)
Likes
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | User who likes the page |
page_id
PK
|
int | Page that is liked |
Primary Key: (user_id, page_id)
Input & Output
Example 1 — Basic Friend Recommendations
Input Tables:
Friendship
| user1_id | user2_id |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 3 | 1 |
Likes
| user_id | page_id |
|---|---|
| 1 | 88 |
| 2 | 23 |
| 2 | 24 |
| 2 | 56 |
| 3 | 24 |
| 3 | 56 |
| 4 | 88 |
Output:
| recommended_page_id |
|---|
| 23 |
| 24 |
| 56 |
💡 Note:
User 1 is friends with users 2, 3, and 4. Friends like pages [23, 24, 56, 88], but user 1 already likes page 88, so we recommend pages 23, 24, and 56.
Example 2 — No New Recommendations
Input Tables:
Friendship
| user1_id | user2_id |
|---|---|
| 1 | 2 |
Likes
| user_id | page_id |
|---|---|
| 1 | 10 |
| 2 | 10 |
Output:
| recommended_page_id |
|---|
💡 Note:
User 1's only friend (user 2) likes page 10, but user 1 already likes page 10, so there are no new recommendations.
Example 3 — No Friends
Input Tables:
Friendship
| user1_id | user2_id |
|---|---|
| 2 | 3 |
Likes
| user_id | page_id |
|---|---|
| 1 | 5 |
| 2 | 10 |
| 3 | 15 |
Output:
| recommended_page_id |
|---|
💡 Note:
User 1 has no friends, so there are no pages to recommend based on friend preferences.
Constraints
-
1 ≤ user_id ≤ 500 -
1 ≤ page_id ≤ 1000 - Each friendship relationship may be represented in either direction
- All user and page IDs are valid integers
Visualization
Tap to expand
Understanding the Visualization
1
Find Friends
Identify all friends of user 1
2
Join Likes
Connect friends to pages they like
3
Filter
Remove pages user 1 already likes
Key Takeaway
🎯 Key Insight: Use JOINs to connect related data and subqueries to filter unwanted results
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code