240823_TIL

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

SQL 코드카타

문제 링크

WITH emp
     AS (SELECT e.employee_id,
                p.project_id,
                e.NAME,
                e.team,
                p.workload
         FROM   employees e
                JOIN project p
                  ON e.employee_id = p.employee_id),
     team
     AS (SELECT e.team,
                Avg(p.workload) AS "avg_team"
         FROM   employees e
                JOIN project p
                  ON e.employee_id = p.employee_id
         GROUP  BY 1)
SELECT emp.employee_id,
       emp.project_id,
       emp.NAME     AS "EMPLOYEE_NAME",
       emp.workload AS "PROJECT_WORKLOAD"
FROM   emp
       LEFT JOIN team
              ON emp.team = team.team
WHERE  workload > avg_team
ORDER  BY 1,
          2;

문제 링크
난이도는 medium이었는데 진짜 너무 헷갈렸던ㅋㅋ
이거는 나중에 다시 풀어봐야겠다.

WITH cte
     AS (SELECT user_id1 AS USER_ID,
                user_id2 AS FRIEND_ID
         FROM   friends
         UNION ALL
         SELECT user_id2 AS USER_ID,
                user_id1 AS FRIEND_ID
         FROM   friends),
     friend_user
     AS (SELECT A.user_id AS USER_ID_1,
                B.user_id AS USER_ID_2
         FROM   cte A
                INNER JOIN cte B
                        ON A.friend_id = B.friend_id
                           AND A.user_id < B.user_id
         ORDER  BY 1,
                   2)
SELECT *
FROM   friends
WHERE  ( user_id1, user_id2 ) NOT IN (SELECT user_id_1,
                                             user_id_2
                                      FROM   friend_user
                                      UNION ALL
                                      SELECT user_id_2,
                                             user_id_1
                                      FROM   friend_user)
ORDER  BY 1,
          2

문제 링크

SELECT Substring_index(email, '@', -1) AS "email_domain",
       Count(*)                        AS "count"
FROM   emails
WHERE  Substring_index(email, '@', -1) LIKE '%.com'
GROUP  BY 1
ORDER  BY 1;

문제 링크
난이도는 hard였는데 정작 별로 어렵진 않았던 문제.
연속된 세션이라는 표현에 버튼 눌려서 window함수 써야하나 허둥댔는데
생각해보니 그럴 필요가 딱히 없었다 (머쓱)

SELECT DISTINCT s1.user_id
FROM   sessions s1
       JOIN sessions s2
         ON s1.user_id = s2.user_id
            AND s1.session_id <> s2.session_id
            AND s1.session_type = s2.session_type
            AND s2.session_end >= s1.session_end
WHERE  Timestampdiff(hour, s1.session_end, s2.session_start) <= 12
ORDER  BY 1;

문제 링크
이게 무슨 개소린가 싶어서 한참을 들여다봤던 문제.
문제 자체를 이해하는 게 너무...뭐랄까 짜쳤는데;

어쨌거나 이해한 범위 내에서 문제를 해결해 보기로.

window함수 중 max() over()라는 게 있다.
이걸 쓰면 각 row에 대해 현재 row까지 나타난 값 중 최대값을 뽑아준다.
여기서 order by 뒤의 조건에 따라 지금까지의 row 중 최대값인지,
앞으로의 row 중 최대값일 값인지가 결정된다고 보면 된다.

먼저 이걸 써서 최대값 조합들을 뽑아본다.

SELECT *,
       Max(height)
         OVER(
           ORDER BY id ASC)  AS left_highest_bar,
       Max(height)
         OVER(
           ORDER BY id DESC) AS right_highest_bar
FROM   heights

결과는 아래와 같이 나온다.

이후에 이 결과를 CTE로 만들어놓고
least를 써서 left_highest_bar와 right_highest_bar 중 작은 값을 뽑는다.
그런 다음 height를 빼서 더하면 빗물을 저장할 수 있는 총량이 나온다.

아래는 정답 쿼리.

WITH result
     AS (SELECT *,
                Max(height)
                  OVER(
                    ORDER BY id ASC)  AS left_highest_bar,
                Max(height)
                  OVER(
                    ORDER BY id DESC) AS right_highest_bar
         FROM   heights)
SELECT Sum(Least(left_highest_bar, right_highest_bar) - height) AS
       "total_trapped_water"
FROM   result;

근데 만약 이런 문제가 코딩테스트에 나오면 걍 틀릴 거 같다...
종이에 그림 그려가면서 문제를 이해하는 데만 한참 걸려서^_ㅠ 씁

풀어지지 말고 매일 해야 할 일을 조금씩 하자.
아직 얻은 건 아무것도 없다.