250207_TIL

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

SQL 문제풀이 복습

---

Leetcode

1919. Leetcodify Similar Friends
풀이시간 03:50

WITH similar
     AS (SELECT l1.user_id                 AS "user1_id",
                l2.user_id                 AS "user2_id",
                l1.day,
                Count(DISTINCT l1.song_id) AS "cnt"
         FROM   Listens l1
                JOIN Listens l2
                  ON l1.user_id <> l2.user_id
                     AND l1.song_id = l2.song_id
                     AND l1.day = l2.day
         GROUP  BY 1,
                   2,
                   3
         HAVING cnt >= 3)
SELECT s.user1_id,
       s.user2_id
FROM   similar s
       JOIN Friendship f
         ON s.user1_id = f.user1_id
            AND s.user2_id = f.user2_id
GROUP  BY 1,
          2;

1934. Confirmation Rate
풀이시간 02:14

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

1939. Users That Actively Request Confirmation Messages
풀이시간 05:26

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, c1.time_stamp, c2.time_stamp) / 60 / 60 / 24 <= 1;

1949. Strong Friendship
풀이시간 15:00

cte를 만들면서 중간중간 끊어 가야지
한 번에 서브쿼리로 이으려고 하면 time limit exceeded가 뜬다.

WITH all_friend
     AS (SELECT user1_id AS "user_id",
                user2_id AS "friend"
         FROM   Friendship
         UNION ALL
         SELECT user2_id,
                user1_id
         FROM   Friendship),
     result
     AS (SELECT LEAST(a1.user_id, a2.user_id)    AS "user1_id",
                GREATEST(a1.user_id, a2.user_id) AS "user2_id",
                Count(DISTINCT a1.friend)        AS "common_friend"
         FROM   all_friend a1
                JOIN all_friend a2
                  ON a1.user_id <> a2.user_id
                     AND a1.friend = a2.friend
         GROUP  BY 1,
                   2
         HAVING common_friend >= 3)
SELECT result.user1_id,
       result.user2_id,
       common_friend
FROM   result
       JOIN Friendship f
         ON result.user1_id = f.user1_id
            AND result.user2_id = f.user2_id;

1951. All the Pairs With the Maximum Number of Common Followers
풀이시간 03:06

WITH result
     AS (SELECT r1.user_id                     AS "user1_id",
                r2.user_id                     AS "user2_id",
                Count(DISTINCT r1.follower_id) AS "cnt"
         FROM   Relations r1
                JOIN Relations r2
                  ON r1.user_id < r2.user_id
                     AND r1.follower_id = r2.follower_id
         GROUP  BY 1,
                   2)
SELECT user1_id,
       user2_id
FROM   result
WHERE  cnt = (SELECT Max(cnt)
              FROM   result);

---

다시 풀어볼 문제

1892. Page Recommendations II
풀이시간 12:11

SELECT b.user_id,
       b.page_id,
       COUNT(*) AS "friends_likes"
FROM   (SELECT a.user_id,
               page_id
        FROM   (SELECT user1_id AS "user_id",
                       user2_id AS "friend"
                FROM   Friendship
                UNION ALL
                SELECT user2_id,
                       user1_id
                FROM   Friendship) a
               LEFT JOIN Likes l
                      ON a.friend = l.user_id) b
       LEFT JOIN Likes l
              ON b.user_id = l.user_id
                 AND b.page_id = l.page_id
WHERE  l.user_id IS NULL
GROUP  BY 1,
          2;

1917. Leetcodify Friends Recommendations
풀이시간 09:39

WITH result
     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.user_id <> l2.user_id
                     AND l1.song_id = l2.song_id
                     AND l1.day = l2.day
         GROUP  BY 1,
                   2,
                   3)
SELECT r.user_id,
       r.recommended_id
FROM   result r
       LEFT JOIN Friendship f
              ON r.user_id = f.user1_id
                 AND r.recommended_id = f.user2_id
       LEFT JOIN Friendship f2
              ON r.recommended_id = f2.user1_id
                 AND r.user_id = f2.user2_id
WHERE  cnt >= 3
       AND f.user1_id IS NULL
       AND f2.user1_id IS NULL
GROUP  BY 1,
          2;