Table: RequestAccepted
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| requester_id | int |
| accepter_id | int |
| accept_date | date |
+----------------+---------+
(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.
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:
| 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 |
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?
문제 정의
- 가장 친구가 많은 ID 와 그 친구의 수를 가져오자
첫번째로 친구 요청을 한 친구의 수와 ID를 가져온다.
select requester_id
,count(requester_id)
from RequestAccepted
group by requester_id
| requester_id | count(requester_id) |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
두번째로 친구 요청을 받은 친구의 수와 ID를 가져온다.
select accepter_id
,count(requester_id)
from RequestAccepted
group by accepter_id
| accepter_id | count(requester_id) |
|---|---|
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
with many_person as
((
select requester_id
,count(requester_id) as num
from RequestAccepted
group by requester_id
)
union all
(
select accepter_id
,count(requester_id)
from RequestAccepted
group by accepter_id
))
select requester_id as id , sum(num) as num
from many_person
group by requester_id
order by num desc
LIMIT 1Output:
| id | num |
|---|---|
| 3 | 3 |
