241211_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
1트(8/22) 때는 sum과 case when을 섞어 쓰는 게 익숙하지 않았었나?
간단하게 해결될 수 있는 문제였는데 cte를 엄청 많이 만들어 놨었네;

SELECT age_bucket,
       ROUND(100.0 * SUM(CASE
                           WHEN activity_type = 'send' THEN time_spent
                         end) / SUM(time_spent), 2)       AS "send_perc",
       100 - ROUND(100.0 * SUM(CASE
                                 WHEN activity_type = 'send' THEN time_spent
                               end) / SUM(time_spent), 2) AS "open_perc"
FROM   Activities ac
       JOIN Age ag
         ON ac.user_id = ag.user_id
GROUP  BY 1;

문제 링크
난이도는 hard인데 별로 하드하진 않은 문제.
join만 잘 해 주면 where절 조건으로 어렵지 않게 발라낼 수 있다.

SELECT p.EMPLOYEE_ID,
       p.PROJECT_ID,
       e.name   AS "EMPLOYEE_NAME",
       workload AS "PROJECT_WORKLOAD"
FROM   Project p
       JOIN Employees e
         ON p.employee_id = e.employee_id
       JOIN (SELECT team,
                    AVG(workload) AS "avg_load"
             FROM   Project p
                    JOIN Employees e
                      ON p.employee_id = e.employee_id
             GROUP  BY 1) a
         ON e.team = a.team
WHERE  workload > avg_load
ORDER  BY 1,
          2;

문제 링크
지난번 풀었을 때도 느꼈지만
체감 난이도는 이게 훨씬 더 hard에 가깝다.
mutual cte를 만들기까지가 굉장히 헷갈리는데,
특히 join의 조건으로 a1.user < a2.user를 써 주는 부분이 포인트.

그 후의 본 쿼리에서도 where절에 서브쿼리를 넣어서
mutual에 해당하는 "모든" 조합을 다 제외해야 하기 때문에
union all을 꼭 포함시켜야 한다. 안 그러면 일부만 제외되기 때문에 오류가 생김.

WITH all_friend
     AS (SELECT user_id1 AS "user",
                user_id2 AS "friend"
         FROM   Friends
         UNION ALL
         SELECT user_id2,
                user_id1
         FROM   Friends),
     mutual
     AS (SELECT a1.USER AS "user1",
                a2.USER AS "user2"
         FROM   all_friend a1
                JOIN all_friend a2
                  ON a1.friend = a2.friend
                     AND a1.USER < a2.USER)
SELECT user_id1,
       user_id2
FROM   Friends
WHERE  ( user_id1, user_id2 ) NOT IN (SELECT user1,
                                             user2
                                      FROM   mutual
                                      UNION ALL
                                      SELECT user2,
                                             user1
                                      FROM   mutual)
ORDER  BY 1,
          2;