241102_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
count와 case when을 조합해서 쓰는 문제.

SELECT s.user_id,
       ROUND(COUNT(CASE
                     WHEN action = 'confirmed' THEN c.user_id
                   end) / COUNT(*), 2) AS "confirmation_rate"
FROM   Signups s
       LEFT JOIN Confirmations c
              ON s.user_id = c.user_id
GROUP  BY 1;

문제 링크
셀프 join을 할 때 시간 조건을 주는 문제.

24시간은 24시간 60분 60초 = 86400초이므로,
timestampdiff를 써서 86400초 이내에 들어온 경우의 user_id만 뽑으면 된다.

SELECT DISTINCT c1.user_id
FROM   Confirmations c1
       JOIN Confirmations c2
         ON c1.user_id = c2.user_id
            AND c1.time_stamp > c2.time_stamp
            AND TIMESTAMPDIFF(second, c2.time_stamp, c1.time_stamp) <= 86400;

문제 링크
1트(8/30) 때에 비해 cte 갯수는 하나 줄이고
서브쿼리로 해결했다는 점이 차이점. 그 외엔 달라진 건 없다.

1. all_friend cte를 만들어서 id별로 모든 친구를 다 구한 다음에
2. all_friend끼리 셀프 조인해서 common_friend가 3 이상인 경우를 뽑고
3. 마지막으로 그 결과를 서브쿼리 a로 묶고 Friendship 테이블과 조인해서 이들끼리도 진짜 친구인지 여부를 확인하면 완성.

WITH all_friend
     AS (SELECT user1_id,
                user2_id
         FROM   Friendship
         UNION ALL
         SELECT user2_id,
                user1_id
         FROM   Friendship)
SELECT a.user1_id,
       a.user2_id,
       common_friend
FROM   (SELECT a1.user1_id,
               a2.user1_id AS "user2_id",
               Count(*)    AS "common_friend"
        FROM   all_friend a1
               JOIN all_friend a2
                 ON a1.user1_id <> a2.user1_id
                    AND a1.user2_id = a2.user2_id
        WHERE  a1.user1_id < a2.user1_id
        GROUP  BY 1,
                  2
        HAVING common_friend >= 3
        ORDER  BY 1,
                  3) a
       JOIN Friendship f
         ON a.user1_id = f.user1_id
            AND a.user2_id = f.user2_id;