[LeetCode] friend-requests-ii-who-has-the-most-friends

WITH temp AS (
SELECT id,
    SUM(cnt) AS num
FROM (
    (SELECT requester_id AS id,
        COUNT(accepter_id) AS cnt
    FROM RequestAccepted
    GROUP BY requester_id)
    UNION ALL
    (SELECT accepter_id AS id,
        COUNT(requester_id) AS cnt
    FROM RequestAccepted
    GROUP BY accepter_id)
) a
GROUP BY id
)

SELECT id, num
FROM temp
WHERE num = (SELECT MAX(num) FROM temp)

1. UNION ALL을 이용해서 requester_id와 accepter_id를 모두 COUNT 해준 후 중복을 살린 채로 병합

2. 해당 결과를 FROM절 서브쿼리로 사용해서 ID별 친구수 테이블을 만듦

3. 해당 결과를 WITH절을 사용해서 테이블 temp로 빼버리고, num이 MAX(num)과 같은 경우에 대해서만 WHERE절을 통해 걸러줌

 

더 쉬운 풀이를 알아보자

1. RequestAccepted 테이블에서 requester_id와 accepter_id만 따로 빼낸 후 이를 병합

2. 이 결과를 with 절로 빼줌

3. GROUP BY, ORDER BY, LIMIT 를 사용해서 잘라줌

WITH temp AS 
(SELECT requester_id AS id
FROM RequestAccepted
UNION ALL
SELECT accepter_id AS id
FROM RequestAccepted)

SELECT id, COUNT(*)
FROM temp
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1