Page Recommendations II

Page Recommendations II - Problem

Database Hard

You are implementing a page recommendation system for a social media website. Your system will recommend a page to user_id if the page is liked by at least one friend of user_id and is not liked by user_id.

Given two tables:

Friendship: Contains friend relationships between users
Likes: Contains which users like which pages

Write a solution to find all the possible page recommendations for every user. Each recommendation should include:

user_id: The ID of the user receiving the recommendation
page_id: The ID of the recommended page
friends_likes: The number of friends who like this page

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 Recommendations

Input Tables:

Friendship

user1_id	user2_id
1	2
1	3
1	4
2	3
2	4
2	5
6	1

Likes

user_id	page_id
1	88
2	23
3	24
4	56
5	11
6	33
2	77
3	77
6	88

Output:

user_id	page_id	friends_likes
1	23	1
1	24	1
1	33	1
1	56	1
1	77	2
2	11	1
2	24	1
2	56	1
2	88	1
3	23	1
3	88	1
4	23	1
4	77	1
4	88	1
5	23	1
5	77	1

💡 Note:

User 1 is friends with users 2, 3, 4, and 6 (bidirectional). User 1 likes page 88, so we recommend pages liked by friends but not by user 1. Page 77 is recommended with friends_likes=2 because both user 2 and user 3 like it.

Example 2 — No Recommendations

Input Tables:

Friendship

user1_id	user2_id
1	2

Likes

user_id	page_id
1	88
2	88

Output:

user_id	page_id	friends_likes

💡 Note:

Users 1 and 2 are friends and both like the same page (88). Since each user already likes the page their friend likes, there are no recommendations to make.

Constraints

1 ≤ user1_id, user2_id ≤ 500
1 ≤ user_id ≤ 500
1 ≤ page_id ≤ 1000
All relationships in Friendship are unique
All pairs in Likes are unique

Visualization

Tap to expand

Asked in

M Meta 28 L LinkedIn 22 T Twitter 15

Use CTE with UNION to create bidirectional friendship mapping, then JOIN with Likes and filter out user's existing likes using NOT EXISTS, finally GROUP BY to count friend likes.

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)

Common Approaches

✓ CTE with Bidirectional Friendship

⏱️ Time: O(F + L log L) Space: O(F + L)

First create a complete friendship mapping (both directions), then join with likes to find pages liked by friends but not by the user themselves, counting the number of friend likes.

CTE with Bidirectional Friendship — Algorithm Steps

Step 1: Create bidirectional friendship CTE using UNION
Step 2: Join friends with their liked pages
Step 3: Filter out pages already liked by the target user
Step 4: Group by user and page to count friend likes

Visualization

Tap to expand

Step-by-Step Walkthrough

Bidirectional

Create complete friendship mapping

Join Likes

Find pages liked by friends

Filter & Count

Exclude user's likes, count friends

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(F + L log L)

F friendships + L likes with grouping

⚡ Linearithmic

Space Complexity

O(F + L)

Store friendship mapping and likes

✓ Linear Space

32.0K Views

Medium Frequency

~25 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

user_id	page_id	friends_likes
1	23	1
1	24	1
1	33	1
1	56	1
1	77	2
2	11	1
2	24	1
2	56	1
2	88	1
3	23	1
3	88	1
4	23	1
4	77	1
4	88	1
5	23	1
5	77	1

user_id	page_id	friends_likes
1	23	1
1	24	1
1	33	1
1	56	1
1	77	2
2	11	1
2	24	1
2	56	1
2	88	1
3	23	1
3	88	1
4	23	1
4	77	1
4	88	1
5	23	1
5	77	1

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with Bidirectional Friendship — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler

user_id	page_id	friends_likes
1	23	1
1	24	1
1	33	1
1	56	1
1	77	2
2	11	1
2	24	1
2	56	1
2	88	1
3	23	1
3	88	1
4	23	1
4	77	1
4	88	1
5	23	1
5	77	1