sql notes: leetcode#602 friend requests 2: who has the most friends

Problem

In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well.

Table request_accepted holds the data of friend acceptance, while requester_id and accepter_id both are the id of a person.

Write a query to find the the people who has most friends and the most friends number. For the sample data above, the result is:

Note:

• It is guaranteed there is only 1 people having the most friends.
• The friend request could only been accepted once, which mean there is no multiple records with the same requester_id and accepter_id value.

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

Analysis

Count the number of each id in both column requester_id and accepter_id, then sort by the number in descending order and output the entry having the maximum number finally.

Concat column requester_id and column accepter_id as a new column with UNION:

1
2
3

SELECT requester_id AS id FROM request_accepted
UNION ALL
SELECT accepter_id AS id FROM request_accepted

Then group by id and count the number of each id:

1
2
3
4
5

SELECT t.id, COUNT(t.id) AS num FROM
(SELECT requester_id AS id FROM request_accepted
 UNION ALL
 SELECT accepter_id AS id FROM request_accepted) AS t
GROUP BY t.id

Finally, sort by the number of each id in descending order and output the first entry:

1
2
3
4
5
6

SELECT t.id, COUNT(t.id) AS num FROM
(SELECT requester_id AS id FROM request_accepted
 UNION ALL
 SELECT accepter_id AS id FROM request_accepted) AS t
GROUP BY t.id
ORDER BY num DESC LIMIT 1;

Solution

1
2
3
4
5
6

SELECT t.id, COUNT(t.id) AS num FROM
(SELECT requester_id AS id FROM request_accepted
 UNION ALL
 SELECT accepter_id AS id FROM request_accepted) AS t
GROUP BY t.id
ORDER BY num DESC LIMIT 1;

Link

602. Friend Requests 2: Who Has the Most Friends
(中文版) SQL 笔记: Leetcode#602 Friend Requests 2: Who Has the Most Friends