240901_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타

문제 링크
이것도 예전에 잘 안풀려서 묵혀놨다가
오늘 풀었더니 생각 외로 간단하게 풀렸던 hard 문제.

조인Join을 한 번에 깔끔하게 완성해야 한다는 생각 없이
CTE를 한 번 더 만든다고 생각하면 크게 어렵진 않다.
다만 이것 외에도 더 간단하게 풀 수 있는 방법이
있긴 있을 것 같아서, 나중에 한 번 더 시도해 볼 예정.

WITH base
     AS (SELECT user1_id,
                user2_id
         FROM   friendship
         UNION ALL
         SELECT user2_id,
                user1_id
         FROM   friendship
         ORDER  BY 1,
                   2),
     result
     AS (SELECT b.user1_id AS "user_id",
                page_id
         FROM   base b
                LEFT JOIN likes l
                       ON b.user2_id = l.user_id)
SELECT r.user_id,
       r.page_id,
       Count(r.page_id) AS "friends_likes"
FROM   result r
       LEFT JOIN likes l
              ON r.user_id = l.user_id
                 AND r.page_id = l.page_id
WHERE  l.user_id IS NULL
GROUP  BY 1,
          2;

문제 링크
처음에 제출했던 쿼리.

테스트 케이스는 통과였는데
제출하고 보니 커버하지 못하는 케이스가 있어서 오답이었다.

WITH all_friendship
     AS (SELECT user1_id,
                user2_id
         FROM   friendship
         UNION ALL
         SELECT user2_id,
                user1_id
         FROM   friendship),
     a
     AS (SELECT l1.user_id                 AS "user_id",
                l2.user_id                 AS "recommended_id",
                Count(DISTINCT l1.song_id) AS "cnt"
         FROM   listens l1
                JOIN listens l2
                  ON l1.day = l2.day
                     AND l1.user_id < l2.user_id
                     AND l1.song_id = l2.song_id
         GROUP  BY 1,
                   2
         HAVING cnt >= 3),
     result
     AS (SELECT user_id,
                recommended_id
         FROM   a
                LEFT JOIN friendship f
                       ON a.user_id = f.user1_id
                          AND a.recommended_id = f.user2_id
         WHERE  user1_id IS NULL) SELECT user_id,
       recommended_id
FROM   result
UNION ALL
SELECT recommended_id,
       user_id
FROM   result;

아래는 수정한 정답 쿼리.
'같은 날'에 3곡 이상의 같은 곡을 들어야
추천 조합에 올라갈 수 있으므로,
CTE a를 만들 때 day까지도 group by의 조건으로 넣어야 한다.

안 그러면 서로 다른 날에 들었더라도
좌우간 3곡 이상만 같은 노래를 들으면
추천 조합으로 묶이기 때문에 오답.

join과 group by의 조건을 잘 살펴볼 것.
그리고 마지막 결과를 뽑을 때
중복되는 조합은 없애야 하므로
union all이 아니라 그냥 union을 써야 하는 점에도 주의.

WITH all_friendship
     AS (SELECT user1_id,
                user2_id
         FROM   friendship
         UNION ALL
         SELECT user2_id,
                user1_id
         FROM   friendship),
     a
     AS (SELECT l1.user_id                 AS "user_id",
                l2.user_id                 AS "recommended_id",
                l1.day,
                Count(DISTINCT l1.song_id) AS "cnt"
         FROM   listens l1
                JOIN listens l2
                  ON l1.day = l2.day
                     AND l1.user_id < l2.user_id
                     AND l1.song_id = l2.song_id
         GROUP  BY 1,
                   2,
                   3
         HAVING cnt >= 3),
     result
     AS (SELECT user_id,
                recommended_id
         FROM   a
                LEFT JOIN friendship f
                       ON a.user_id = f.user1_id
                          AND a.recommended_id = f.user2_id
         WHERE  user1_id IS NULL) SELECT user_id,
       recommended_id
FROM   result
UNION
SELECT recommended_id,
       user_id
FROM   result;