Friend Requests II: Who Has the Most Friends

You can write a query to find the person who has the most friends by counting the number of friends for each user and then selecting the user with the maximum number. Here’s the query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT id, num
FROM (
  SELECT person_id AS id, COUNT(*) AS num
  FROM (
    SELECT requester_id AS person_id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS person_id FROM RequestAccepted
  ) AS friends
  GROUP BY person_id
) AS friend_count
ORDER BY num DESC
LIMIT 1;

Explanation:

  • The subquery inside the FROM clause first combines requester_id and accepter_id from the RequestAccepted table using the UNION ALL operator. This ensures that we consider both the requesters and accepters as potential friends.
  • Then, the outer query groups the results by person_id and counts the number of friends for each person.
  • Finally, the main query orders the results by the friend count in descending order and selects the first row using LIMIT 1, which gives the person with the most friends.