SQL | LeetCode 602. 가장 친구가 많은 사람 찾기

 

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?

1. 요청자와 수락자 모두를 하나의 열로 모으기

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

2. 각 id별로 몇 번 나오는지 세기 (즉, 친구 수 계산)

SELECT 
  id,
  COUNT(*) AS num
FROM (
  SELECT requester_id AS id FROM RequestAccepted
  UNION ALL
  SELECT accepter_id AS id FROM RequestAccepted
) AS all_friends
GROUP BY id

3. 가장 친구가 많은 사람 1명만 뽑기

ORDER BY num DESC
LIMIT 1;

보너스: 친구 수가 같은 사람이 여러 명인 경우 (동점 처리)

RequestAccepted 테이블:
+--------------+-------------+-------------+
| 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  |

 

Step 1: requester_id와 accepter_id를 같은 열로 합치기

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

Step 2: 각 id별로 몇 번 나왔는지 세기 (친구 수 계산)

SELECT 
  id,
  COUNT(*) AS num
FROM (
  SELECT requester_id AS id FROM RequestAccepted
  UNION ALL
  SELECT accepter_id AS id FROM RequestAccepted
) AS all_friends
GROUP BY id

Step 3: 최대 친구 수 계산

SELECT MAX(num) AS max_num FROM friend_counts

Step 4: 최대 친구 수와 같은 사람들만 선택

SELECT 
  id,
  num
FROM friend_counts
WHERE num = (SELECT max_num FROM max_count)

WITH friend_counts AS (
  SELECT 
    id,
    COUNT(*) AS num
  FROM (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id FROM RequestAccepted
  ) AS all_friends
  GROUP BY id
),
max_count AS (
  SELECT MAX(num) AS max_num FROM friend_counts
)
SELECT 
  id, 
  num
FROM friend_counts
WHERE num = (SELECT max_num FROM max_count);