Page Recommendations II - Practice Coding Problems

Page Recommendations II - Problem

Database Hard

You're building a smart page recommendation system for a social media platform! Your mission is to help users discover new content by leveraging their social connections.

Given two tables:

Friendship - stores bidirectional friendships between users
Likes - tracks which users like which pages

Your system should recommend a page to a user if:

At least one of their friends likes the page
The user doesn't already like the page themselves

For each valid recommendation, return the user_id, page_id, and friends_likes (how many friends like that page).

Example: If Alice is friends with Bob and Charlie, and both Bob and Charlie like "Tech News" page, but Alice doesn't, then recommend "Tech News" to Alice with friends_likes = 2.

Input & Output

example_1.sql — Basic Recommendations

$ Input: Friendship: [[1,2],[1,3],[1,4],[2,3],[2,4],[3,4]]\nLikes: [[1,21],[1,22],[2,21],[3,21],[4,22]]

› Output: [[1,null],[2,22],[3,22],[4,21]]

💡 Note: User 1 has no recommendations (already likes pages 21,22). User 2 gets page 22 (friend 1 likes it). User 3 gets page 22 (friend 1 likes it). User 4 gets page 21 (friends 2,3 like it, so friends_likes=2).

example_2.sql — Multiple Friend Likes

$ Input: Friendship: [[1,2],[1,3]]\nLikes: [[1,22],[2,21],[2,22],[3,21]]

› Output: [[1,21],[2,null],[3,22]]

💡 Note: User 1 gets page 21 (friends 2,3 both like it, friends_likes=2). User 2 has no new recommendations. User 3 gets page 22 (friend 2 likes it).

example_3.sql — No Friendships

$ Input: Friendship: []\nLikes: [[1,21],[2,22],[3,21]]

› Output: []

💡 Note: No friendships exist, so no recommendations can be made.

Visualization

Tap to expand

Understanding the Visualization

Map All Friendships

Create a complete bidirectional friendship graph using UNION ALL

Connect Friends to Likes

JOIN friendships with likes to see what friends enjoy

Exclude Existing Preferences

LEFT JOIN with user's own likes and filter NULL to find new recommendations

Count and Recommend

GROUP BY to count how many friends like each recommended page

Key Takeaway

🎯 Key Insight: Use SQL JOINs strategically - bidirectional CTE for friendships, LEFT JOIN exclusion for filtering, and GROUP BY for counting friend recommendations efficiently.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Efficient JOINs with proper indexing on user_id and page_id columns

⚡ Linearithmic

Space Complexity

O(n)

Temporary space for JOIN operations and result aggregation

⚡ Linearithmic Space

Constraints

1 ≤ user1_id, user2_id ≤ 10⁵
1 ≤ page_id ≤ 10⁵
1 ≤ Number of friendships ≤ 5 × 10⁴
1 ≤ Number of likes ≤ 10⁵
Each friendship is unique and bidirectional
Each user-page like is unique

Asked in

f Meta 45 in LinkedIn 38 t Twitter 32 📷 Instagram 28

The optimal solution uses efficient SQL JOINs with a bidirectional friendship CTE and LEFT JOIN exclusion pattern to find friend recommendations in O(n log n) time. The key insight is leveraging SQL's query optimizer rather than nested subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized JOIN Query (Optimal)	O(n log n)	O(n)	Use efficient JOINs with LEFT JOIN exclusion pattern to solve in one pass
Brute Force (Nested Subqueries)	O(n³)	O(1)	Use nested subqueries to check each user-page combination individually

Optimized JOIN Query (Optimal) — Algorithm Steps

Create bidirectional friendship relationships using UNION
JOIN friendships with likes to get friend-page combinations
LEFT JOIN with user's own likes to identify pages they don't like
Filter out matches and GROUP BY to count friend likes

Visualization

Tap to expand

Step-by-Step Walkthrough

Create Bidirectional Friendships

UNION ALL creates complete friendship graph

JOIN with Friend Likes

Connect friends to their liked pages

LEFT JOIN User Likes

Identify pages user doesn't already like

Filter and Count

WHERE IS NULL filters and GROUP BY counts

Code -

solution.c — C

// Optimal SQL Solution using efficient JOINs
#include <stdio.h>

int main() {
    const char* query = 
    "WITH bidirectional_friendship AS (\n"
    "    SELECT user1_id as user_id, user2_id as friend_id FROM Friendship\n"
    "    UNION ALL\n"
    "    SELECT user2_id as user_id, user1_id as friend_id FROM Friendship\n"
    ")\n"
    "SELECT \n"
    "    bf.user_id,\n"
    "    fl.page_id,\n"
    "    COUNT(*) as friends_likes\n"
    "FROM bidirectional_friendship bf\n"
    "JOIN Likes fl ON bf.friend_id = fl.user_id\n"
    "LEFT JOIN Likes ul ON bf.user_id = ul.user_id AND fl.page_id = ul.page_id\n"
    "WHERE ul.user_id IS NULL\n"
    "GROUP BY bf.user_id, fl.page_id\n"
    "ORDER BY bf.user_id, fl.page_id";
    
    printf("%s\n", query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Efficient JOINs with proper indexing on user_id and page_id columns

⚡ Linearithmic

Space Complexity

O(n)

Temporary space for JOIN operations and result aggregation

⚡ Linearithmic Space

Constraints

1 ≤ user1_id, user2_id ≤ 10⁵
1 ≤ page_id ≤ 10⁵
1 ≤ Number of friendships ≤ 5 × 10⁴
1 ≤ Number of likes ≤ 10⁵
Each friendship is unique and bidirectional
Each user-page like is unique

23.4K Views

High Frequency

~25 min Avg. Time

856 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Optimized JOIN Query (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler