All the Pairs With the Maximum Number of Common Followers

All the Pairs With the Maximum Number of Common Followers - Problem

Database Medium

Find User Pairs with Maximum Common Followers

You're analyzing a social media platform where users follow each other. Given a Relations table that tracks follower relationships, your task is to find all pairs of users who share the maximum number of common followers.

The Relations table has the following structure:

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| follower_id | int  |
+-------------+------+

Each row indicates that follower_id is following user_id. You need to:
1. Calculate how many common followers each pair of users has
2. Find the maximum number of common followers among all pairs
3. Return all user pairs that achieve this maximum

Output format: Return pairs as (user1_id, user2_id) where user1_id < user2_id

Input & Output

example_1.sql — Basic Case

$ Input: Relations table: | user_id | follower_id | |---------|-------------| | 1 | 3 | | 1 | 4 | | 2 | 3 | | 2 | 4 | | 2 | 5 | | 6 | 1 |

› Output: | user1_id | user2_id | |----------|----------| | 1 | 2 |

💡 Note: Users 1 and 2 have 2 common followers (3 and 4). This is the maximum number of common followers among all possible pairs, so (1,2) is returned.

example_2.sql — Multiple Pairs with Same Max

$ Input: Relations table: | user_id | follower_id | |---------|-------------| | 1 | 5 | | 1 | 6 | | 2 | 5 | | 2 | 6 | | 3 | 5 | | 3 | 6 | | 4 | 7 |

› Output: | user1_id | user2_id | |----------|----------| | 1 | 2 | | 1 | 3 | | 2 | 3 |

💡 Note: Users 1, 2, and 3 all share 2 common followers (5 and 6). All three pairs (1,2), (1,3), and (2,3) have the maximum count of 2, so all are returned.

example_3.sql — Single Follower Maximum

$ Input: Relations table: | user_id | follower_id | |---------|-------------| | 1 | 3 | | 2 | 3 | | 4 | 5 | | 6 | 7 |

› Output: | user1_id | user2_id | |----------|----------| | 1 | 2 |

💡 Note: Only users 1 and 2 have any common followers (follower 3). Since 1 is the maximum number of common followers, pair (1,2) is returned.

Visualization

Tap to expand

Understanding the Visualization

Identify Relationships

Map out all follower-user relationships in the social network

Find Intersections

For each pair of users, find followers they have in common using SQL JOIN

Count & Compare

Count common followers and use window functions to find the global maximum

Select Winners

Return all user pairs that achieve the maximum overlap

Key Takeaway

🎯 Key Insight: Using SQL window functions allows us to find the global maximum in a single pass, making the solution both elegant and efficient for large social networks.

Time & Space Complexity

Time Complexity

⏱️

O(n² log n)

O(n²) to generate pairs and count common followers, O(log n) for sorting operations

⚠ Quadratic Growth

Space Complexity

O(n²)

Space needed to store all user pairs and their common follower counts

⚠ Quadratic Space

Constraints

1 ≤ Relations.length ≤ 10⁴
1 ≤ user_id, follower_id ≤ 10⁹
user_id ≠ follower_id
Each (user_id, follower_id) pair is unique
At least one pair of users must exist

Asked in

f Meta 45 G Google 35 a Amazon 28 ⊞ Microsoft 22

The optimal solution uses Common Table Expressions (CTEs) with window functions to efficiently find user pairs with maximum common followers. First, we join the Relations table on follower_id to count common followers for each user pair where user1_id < user2_id. Then we use MAX() OVER() to find the maximum count across all pairs in a single pass. Finally, we filter pairs matching this maximum count.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force SQL (Multiple Subqueries)	O(n³)	O(n²)	Use nested subqueries to count common followers for each pair
Optimized CTE with Window Functions	O(n² log n)	O(n²)	Use CTEs and window functions to efficiently find common followers and maximum in one query

Brute Force SQL (Multiple Subqueries) — Algorithm Steps

Generate all possible pairs of users where user1_id < user2_id
For each pair, count common followers using correlated subqueries
Find the maximum count of common followers
Filter pairs that have the maximum count

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate Pairs

Create all possible user pairs where user1_id < user2_id

Count Common

For each pair, count common followers using nested queries

Find Maximum

Determine the maximum common follower count

Filter Results

Return all pairs with the maximum count

Code -

solution.c — C

// Brute Force SQL Solution
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sql[] = 
    "WITH UserPairs AS ("
    "    SELECT DISTINCT r1.user_id AS user1_id, r2.user_id AS user2_id"
    "    FROM Relations r1"
    "    CROSS JOIN Relations r2"
    "    WHERE r1.user_id < r2.user_id"
    "),"
    "CommonFollowers AS ("
    "    SELECT "
    "        up.user1_id,"
    "        up.user2_id,"
    "        ("
    "            SELECT COUNT(*)"
    "            FROM Relations r1"
    "            JOIN Relations r2 ON r1.follower_id = r2.follower_id"
    "            WHERE r1.user_id = up.user1_id AND r2.user_id = up.user2_id"
    "        ) AS common_count"
    "    FROM UserPairs up"
    "),"
    "MaxCommon AS ("
    "    SELECT MAX(common_count) AS max_common"
    "    FROM CommonFollowers"
    ")"
    "SELECT user1_id, user2_id"
    "FROM CommonFollowers cf"
    "JOIN MaxCommon mc ON cf.common_count = mc.max_common"
    "ORDER BY user1_id, user2_id;";
    return sql;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

For n users, we have O(n²) pairs, and for each pair we scan the relations table O(n) times

⚠ Quadratic Growth

Space Complexity

O(n²)

Need to store all possible user pairs and their common follower counts

⚠ Quadratic Space

Constraints

1 ≤ Relations.length ≤ 10⁴
1 ≤ user_id, follower_id ≤ 10⁹
user_id ≠ follower_id
Each (user_id, follower_id) pair is unique
At least one pair of users must exist

36.9K Views

Medium Frequency

~25 min Avg. Time

1.6K 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

Brute Force SQL (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler