Friend Requests II: Who Has the Most Friends

Friend Requests II: Who Has the Most Friends

What is this problem about?

The Friend Requests II interview question gives you a single table RequestAccepted containing requester_id and accepter_id. You need to write a SQL query to find the person who has the most friends and the total number of friends they have. A person's friend count is the sum of requests they have accepted PLUS the requests they have sent that were accepted.

Why is this asked in interviews?

Companies like Meta, Amazon, and Bloomberg ask this Database interview pattern to test your ability to normalize data. The friendship relationship is bidirectional, but the data is stored directionally (sender vs. receiver). It evaluates whether you can use a UNION ALL to combine columns into a single unified list before performing aggregations.

Algorithmic pattern used

The solution uses a UNION ALL and Aggregation pattern.

1. Unify: Create a derived table (or CTE) that selects requester_id as user_id and then UNION ALL with a selection of accepter_id as user_id. This essentially lists every instance of a user participating in a friendship.
2. Aggregate: GROUP BY user_id on this derived table and COUNT(*) to get the total number of friends for each user.
3. Sort and Limit: ORDER BY count DESC and LIMIT 1 to find the user with the maximum friends.

Example explanation

Accepted Table: 1 -> 2 1 -> 3 2 -> 3

1. UNION ALL:
   • From requester: [1, 1, 2]
   • From accepter: [2, 3, 3]
   • Combined list: [1, 1, 2, 2, 3, 3]
2. Group & Count:
   • User 1: 2 friends
   • User 2: 2 friends
   • User 3: 2 friends (In this case it's a tie, but usually one user has a clear maximum).

Common mistakes candidates make

• Using UNION instead of UNION ALL: UNION removes duplicates. If user A is friends with B and C, they will appear twice in the unified list. UNION would reduce this to 1, ruining the count.
• Complex Joins: Trying to self-join the table to calculate sent and received separately and then adding them, which is much more verbose and prone to error.
• Handling Ties: The basic LIMIT 1 assumes only one person has the maximum. If the problem asks for all people tied for the maximum, you need a subquery or window function like RANK().

Interview preparation tip

When data represents a symmetric relationship (like a graph edge) but is stored in two columns (source, destination), UNION ALL is the standard technique to "flatten" the data so you can group by the entity regardless of its role in the relationship.