[LeetCode] Friend Requests II

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

LeetCode - The World's Leading Online Programming Learning Platform

 

처음의 실수는 join으로 잡았는데, 한 쪽 테이블에 정보가 없을 거라는 생각을 하지 못했다.
join으로 풀 수야 있긴 한데, 효율적인 풀이는 아닐 것이다.

Union all로 쉽게 해결 가능하다. with문으로 끌어다쓰자.

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
)

SELECT
    ID,
    SUM(NUM) AS NUM
FROM
    FRIENDS_TOTAL
GROUP BY ID
ORDER BY NUM DESC
LIMIT 1