Friend Requests II: Who Has the Most Friends

Friend Requests II: Who Has the Most Friends - Problem

Database Medium

🤝 Friend Requests II: Who Has the Most Friends

You're working on a social media platform's analytics team! Given a database table RequestAccepted that tracks all accepted friend requests, you need to find the person with the most friends.

📊 Table Structure:

Column Name	Type	Description
`requester_id`	int	ID of user who sent the request
`accepter_id`	int	ID of user who accepted the request
`accept_date`	date	When the request was accepted

Key Points:

Each row represents a bidirectional friendship (if A sends to B and B accepts, both A and B are friends)
The combination (requester_id, accepter_id) is unique
You need to return both the person ID and their total friend count
There's guaranteed to be exactly one person with the maximum friends

Goal: Write a SQL query to find who has the most friends and how many friends they have.

Input & Output

example_1.sql — Basic Friend Network

$ Input: RequestAccepted table: | requester_id | accepter_id | accept_date | |--------------|-------------|-------------| | 1 | 2 | 2016-06-03 | | 1 | 3 | 2016-06-08 | | 2 | 3 | 2016-06-08 | | 3 | 4 | 2016-06-09 |

› Output: | id | num | |----|-----| | 3 | 3 |

💡 Note: User 3 has the most friends. They are connected to users 1, 2, and 4. User 3 appears in 3 friendship records: as accepter with users 1 and 2, and as requester with user 4.

example_2.sql — Single Friendship

$ Input: RequestAccepted table: | requester_id | accepter_id | accept_date | |--------------|-------------|-------------| | 1 | 2 | 2016-06-03 |

› Output: | id | num | |----|-----| | 1 | 1 | | 2 | 1 |

💡 Note: Both users have exactly 1 friend each. Either could be returned as they're tied, but the problem guarantees only one person has the maximum.

example_3.sql — Linear Chain

$ Input: RequestAccepted table: | requester_id | accepter_id | accept_date | |--------------|-------------|-------------| | 1 | 2 | 2016-06-03 | | 2 | 3 | 2016-06-04 | | 3 | 4 | 2016-06-05 | | 4 | 5 | 2016-06-06 |

› Output: | id | num | |----|-----| | 2 | 2 | | 3 | 2 | | 4 | 2 |

💡 Note: In a linear chain, users 2, 3, and 4 each have 2 friends (their neighbors), while users 1 and 5 have only 1 friend each.

Visualization

Tap to expand

Understanding the Visualization

Collect All Relationships

Each accepted request creates a bidirectional friendship - both requester and accepter gain a friend

Count Per Person

Use UNION ALL to list each person every time they appear in a friendship, then group and count

Find the Winner

Sort by friend count descending and pick the top person

Key Takeaway

🎯 Key Insight: Friendships are bidirectional, so we count each person's appearances in both requester and accepter roles using UNION ALL, then group and count efficiently.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

UNION ALL is O(n), GROUP BY with sorting is O(n log n) due to internal sorting for aggregation

⚡ Linearithmic

Space Complexity

O(n)

Temporary space needed for the unified result set before grouping

⚡ Linearithmic Space

Constraints

1 ≤ number of accepted requests ≤ 500
1 ≤ requester_id, accepter_id ≤ 500
requester_id ≠ accepter_id (no self-friend requests)
All dates are valid
Guaranteed exactly one person has the maximum number of friends

Asked in

f Facebook 45 in LinkedIn 38 📷 Instagram 25 🐦 Twitter 20

The optimal solution uses UNION ALL to combine both requester_id and accepter_id columns into a single column, then applies GROUP BY with COUNT(*) to efficiently count friendships per user. This approach leverages SQL's built-in aggregation capabilities for maximum performance.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Subqueries for Each User)	O(n²)	O(n)	Count friends for each user individually using separate subqueries
Union All + Group By (Optimal SQL)	O(n log n)	O(n)	Combine both roles into one table, then group and count efficiently

Brute Force (Subqueries for Each User) — Algorithm Steps

Get all unique user IDs from both requester_id and accepter_id columns
For each user, count how many times they appear as requester_id
For each user, count how many times they appear as accepter_id
Sum both counts to get total friends for each user
Find the user with maximum total friends

Visualization

Tap to expand

Step-by-Step Walkthrough

Identify All Users

Scan table to find all unique user IDs from both columns

Count Per User

For each user, scan table twice: once as requester, once as accepter

Find Maximum

Compare all friend counts to find the person with most friends

Code -

solution.c — C

// Simulating the SQL logic in C
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int requester_id;
    int accepter_id;
    char accept_date[20];
} Request;

typedef struct {
    int id;
    int count;
} Result;

Result findMostFriendsBruteForce(Request* requests, int size) {
    int users[1000] = {0}; // Assuming user IDs < 1000
    int userCount = 0;
    
    // Get all unique users
    for (int i = 0; i < size; i++) {
        int found1 = 0, found2 = 0;
        for (int j = 0; j < userCount; j++) {
            if (users[j] == requests[i].requester_id) found1 = 1;
            if (users[j] == requests[i].accepter_id) found2 = 1;
        }
        if (!found1) users[userCount++] = requests[i].requester_id;
        if (!found2) users[userCount++] = requests[i].accepter_id;
    }
    
    int maxFriends = 0;
    int mostPopularUser = -1;
    
    // For each user, count friends manually
    for (int i = 0; i < userCount; i++) {
        int friendCount = 0;
        // Count as requester
        for (int j = 0; j < size; j++) {
            if (requests[j].requester_id == users[i]) friendCount++;
        }
        // Count as accepter
        for (int j = 0; j < size; j++) {
            if (requests[j].accepter_id == users[i]) friendCount++;
        }
        
        if (friendCount > maxFriends) {
            maxFriends = friendCount;
            mostPopularUser = users[i];
        }
    }
    
    Result result = {mostPopularUser, maxFriends};
    return result;
}

int main() {
    Request requests[] = {
        {1, 2, "2016-06-03"},
        {1, 3, "2016-06-08"},
        {2, 3, "2016-06-08"},
        {3, 4, "2016-06-09"}
    };
    
    Result result = findMostFriendsBruteForce(requests, 4);
    printf("ID: %d, Friends: %d\n", result.id, result.count);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of the n unique users, we scan the entire table twice to count their friendships

⚠ Quadratic Growth

Space Complexity

O(n)

Need to store intermediate results for all unique users

⚡ Linearithmic Space

Constraints

1 ≤ number of accepted requests ≤ 500
1 ≤ requester_id, accepter_id ≤ 500
requester_id ≠ accepter_id (no self-friend requests)
All dates are valid
Guaranteed exactly one person has the maximum number of friends

42.0K Views

High Frequency

~12 min Avg. Time

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

🤝 Friend Requests II: Who Has the Most Friends

📊 Table Structure:

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Subqueries for Each User) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler