Leetcode SQL 50 - 602. Friend Requests II: Who Has the Most Friends

문제 사이트: @leetcode

 

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.

 

<나의 정답 쿼리>

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
ORDER BY num DESC
LIMIT 1

 

<내가 생각한 문제 풀이 순서>

1. 테이블 자체가 request & accept 이기 때문에 request user id와 accept user id를 union 하기 (모든 행동을 합쳐서 user id별 num을 구하면 친구 수를 구할 수 있기 때문에)
2. union 한 결과 기준으로 id 별 num을 구해서 친구 수 구하기
3. 친구 수를 내림차순 정렬하여 최대 값만 뽑기