602 Friend Requests II: Who Has the Most Friends

Description

Table: RequestAccepted

• (requester_id, accepter_id) is the primary key (combination of columns with unique values) for this table.
• This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.

Problem Statement

Write a solution to find the people who have the most friends and the most friends number. The test cases are generated so that only one person has the most friends.

The result format is in the following example.

Example 1:

Input:

• RequestAccepted table:

Output:

Explanation:

• The person with id 3 is a friend of people 1, 2, and 4, so he has three friends in total, which is the most number than any others.

Follow up: In the real world, multiple people could have the same most number of friends. Could you find all these people in this case?

Solution

In this problem, you need to find the people who have the most friends and the friends count. You can find the most friends count using both columns: requester_id and accepter_id from the RequestAccepted table. You can union both these fields and then find the count of unique values using COUNT(DISTINCT) function.

1. Find all members of the table RequestAccepted.

1SELECT requester_id AS id FROM RequestAccepted
2UNION
3SELECT accepter_id AS id FROM RequestAccepted

2. Find the most friends count using COUNT(DISTINCT) function.

1SELECT id, COUNT(1) AS num
2    FROM (
3        SELECT requester_id AS id FROM RequestAccepted
4        UNION
5        SELECT accepter_id AS id FROM RequestAccepted
6    )

You need to find only one record who has the most friends. So, you need to limit the result to one record using LIMIT 1 clause.

The final solution would look like this.

1WITH tmp AS (
2    SELECT accepter_id AS id FROM RequestAccepted
3    UNION ALL
4    SELECT requester_id AS id FROM RequestAccepted
5) SELECT id, COUNT(1) As num
6    FROM tmp
7    GROUP BY id
8    ORDER BY num DESC
9    LIMIT 1;