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

1. 문제

 

 

 

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.

 

https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/

 

 

2. 문제 조건

 

1. find the people who have the most friends and the most friends number.

 

-> 친구 요청한 사람, 친구 요청을 수락한 사람 모두 서로의 번호를 갖고 있다.

-> 따라서 요청한 id별 요청 횟수와 수락한 id별 수락 횟수를 모두 세주어야 한다.

 

 

3. 코드 작성

 

-- id별로 요청, 수락 횟수를 모두 합친 테이블
WITH friends_total AS (
    SELECT requester_id AS id
        , COUNT(*) AS num
    FROM RequestAccepted
    GROUP BY id

    UNION ALL

    SELECT accepter_id AS id
         , COUNT(*) AS num
    FROM RequestAccepted
    GROUP BY id
)

-- id별 요청, 수락 횟수 합 중 최대값
SELECT id
     , SUM(num) AS num
FROM friends_total
GROUP BY id
ORDER BY num DESC
LIMIT 1

 

 

 

4. 회고

 

• UNION ALL을 복습하는 계기가 되었다.
• 테이블을 합치는 방법은 UNION, UNION ALL이 있다.
  • UNION은 중복된 값을 제거하며 합치지만, UNION ALL은 모든 값을 유지한채 합친다는 차이점이 있다.
  • mysql의 경우에는 FULL OUTER JOIN이 없기 때문에, LEFT, RIGHT JOIN한 것을 UNION하면 된다.
  • https://jjjk84.tistory.com/25