240712_TIL

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

SQL 코드카타

문제 링크
오랜만에 sql로 피벗테이블을 만드는 문제.

사전캠프 기간에 지급됐던 SQL 기본강의와 지난달 SQL 챌린지 세션에서
피벗테이블 만들기를 조금씩 다뤘던 적이 있었다.
그런데 시간이 오래 지나기도 했고,
결정적으로 "써 볼 일이 없으니" 자연스럽게 까먹게 됐었는데..
이왕 문제로 만난 김에 한 단계씩 정리해 봤다.

먼저 주어진 Student 테이블에
출신 대륙별로 number를 부여하는 row_number를 추가해 준다.
(이 row_number를 넣는 이유는 나중에 피벗테이블을 만들 때
행 구분의 기준이 되는 값을 설정해 놓기 위함이다.)

SELECT *,
       Row_number()
         OVER(
           partition BY continent
           ORDER BY NAME) AS row_id
FROM   student

여기서 row_number 함수를 쓸 때 order by name을 추가해 준 것은
같은 대륙 출신인 학생이 여러 명 있을 경우
알파벳순으로 나열하라는 조건이 있었기 때문.
출력된 결과는 아래와 같다.

이제 이 테이블로부터 피벗테이블처럼 생긴 결과를 뽑으려면
아래와 같은 형태로 쿼리를 짜면 된다.

SELECT Max(CASE
             WHEN continent = 'America' THEN NAME
           END) AS "America",
       Max(CASE
             WHEN continent = 'Asia' THEN NAME
           END) AS "Asia",
       Max(CASE
             WHEN continent = 'Europe' THEN NAME
           END) AS "Europe"
FROM   (SELECT *,
               Row_number()
                 OVER(
                   partition BY continent
                   ORDER BY NAME) AS row_id
        FROM   student) AS t
GROUP  BY row_id;

case when 구문은 예컨대 대륙(continent)이 America인 경우 name을 출력하되,
앞에 Max가 붙어있으므로 name의 최대값, 즉 가장 알파벳순으로 나중인 경우를 출력한다. Asia나 Europe에 대해서도 마찬가지. 뽑힌 이름들에는 컬럼명이 붙어야 하므로 순서대로 America, Asia, Europe 등 대륙명을 써 준다.

그런데 America의 경우 학생이 두 명 있으므로,
쿼리의 가장 마지막에 group by row_id, 즉 인라인뷰 서브쿼리에서 정의했던
그룹별 id를 기준으로 그룹화한다는 조건을 명시해 줘야 한다. 이렇게 해야

• row_id 1번 기준으로 America 이름의 최대값 → Jack 출력
• row_id 2번 기준으로 America 이름의 최대값 → Jane 출력

이 결과가 합쳐지면서 문제에서 요구했던 대로 알파벳순으로 출력되기 때문.
만약 제일 마지막의 group by 이하 구문을 빼먹고 출력하면

이런 결과가 나온다.
그냥 무조건 대륙별로 이름이 알파벳순으로 제일 뒤에 있는 학생을 뽑는 셈.
테이블 형태로 출력하려면 row_id 처럼 행을 구분하는 값을 두는 걸 잊지 말기.

문제 링크

SELECT DISTINCT sub_id                            post_id,
                (SELECT Count(DISTINCT sub_id)
                 FROM   submissions s2
                 WHERE  s1.sub_id = s2.parent_id) number_of_comments
FROM   submissions s1
WHERE  parent_id IS NULL
ORDER  BY sub_id;

문제 링크
한방에 깔끔하게 풀린 hard 문제.

1. 모든 날짜들에 대한 성공/실패 여부를 확인하기 위해
   failed와 succeeded 테이블에서 날짜와 period_state를 지정해 준 뒤
   union을 통해 합치고, 그 결과를 a라는 이름의 CTE에 저장한다.

2. a로부터 데이터를 불러오되 where절에 조건을 추가해서 2019년 데이터만 불러오고, partition 구분 없이 지정된 row_number와 성공/실패에 따라 partition이 구분된 row_number를 지정해 준다. 이렇게 하는 이유는 본 쿼리를 짤 때 필요하기 때문. 여기까지의 결과는 b라는 이름의 CTE에 저장된다.

3. 이제 b로부터 본 쿼리를 작성하는데,
   이 때 그루핑의 기준으로 period_state 외에 date_id-ranking을 추가해 주었다. 예컨대 succeeded로만 그루핑을 하게 되면 중간 날짜에 failed가 들어갔다가 다시 succeeded로 바뀌어도 min, max date를 계산하는 과정에서 succeeded 기준으로 모두 묶여서 계산될 수 있기 때문.
   
   문제에서는 날짜별로 period_state를 구분하고 있기 때문에 그루핑을 할 때 조건을 잘 주어서 묶어야 한다. 아래는 정답 쿼리.

WITH a
     AS (SELECT fail_date AS "date",
                "failed"  AS "period_state"
         FROM   failed
         UNION
         SELECT success_date AS "date",
                "succeeded"  AS "period_state"
         FROM   succeeded
         ORDER  BY 1),
     b
     AS (SELECT date,
                period_state,
                Row_number()
                  OVER (
                    ORDER BY date) AS "date_id",
                Row_number()
                  OVER(
                    partition BY period_state
                    ORDER BY date) AS "ranking"
         FROM   a
         WHERE  Year(date) = '2019'
         ORDER  BY 1)
SELECT period_state,
       Min(date) AS "start_date",
       Max(date) AS "end_date"
FROM   b
GROUP  BY period_state,
          date_id - ranking;

문제 링크
direct로 boss에게 보고하는 경우를 direct CTE,
그 외에 매니저를 한 명씩 거칠 때마다 1step, 2step, 3step의 CTE에 나누어 결과를 저장했다. 모든 CTE에서 employee_id를 뽑아서 union해 주면 정답.

WITH direct
AS
  (
         SELECT employee_id
         FROM   employees
         WHERE  employee_id <> '1'
         AND    manager_id = '1'),
  1step
AS
  (
            SELECT    e1.employee_id
            FROM      employees e1
            LEFT JOIN employees e2
            ON        e1.manager_id = e2.employee_id
            WHERE     e1.manager_id <> '1'
            AND       e2.manager_id = '1'),
  2step
AS
  (
            SELECT    e1.employee_id
            FROM      employees e1
            LEFT JOIN employees e2
            ON        e1.manager_id = e2.employee_id
            LEFT JOIN employees e3
            ON        e2.manager_id = e3.employee_id
            WHERE     e1.manager_id <> '1'
            AND       e3.manager_id = '1'),
  3step
AS
  (
            SELECT    e1.employee_id
            FROM      employees e1
            LEFT JOIN employees e2
            ON        e1.manager_id = e2.employee_id
            LEFT JOIN employees e3
            ON        e2.manager_id = e3.employee_id
            LEFT JOIN employees e4
            ON        e3.manager_id = e4.employee_id
            WHERE     e1.manager_id <> '1'
            AND       e4.manager_id = '1')
  SELECT employee_id
  FROM   direct
  UNION
  SELECT employee_id
  FROM   1step
  UNION
  SELECT employee_id
  FROM   2step
  UNION
  SELECT employee_id
  FROM   3step;

문제 링크

SELECT c.country_name,
       CASE
         WHEN Avg(weather_state) <= '15' THEN "cold"
         WHEN Avg(weather_state) >= '25' THEN "hot"
         ELSE "warm"
       end AS "weather_type"
FROM   weather w
       LEFT JOIN countries c
              ON w.country_id = c.country_id
WHERE  Date_format(day, '%Y-%m') = '2019-11'
GROUP  BY 1;