240829_TIL

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

SQL 코드카타

문제 링크
어제 풀었던 코드카타의 hard 버전.
join 조건 하에서 overlap된 경우를 찾는 건 쉬웠는데,
max_overlapping_shifts를 구하는 게 어려웠다.

아래는 테스트 케이스는 통과했는데 제출 결과 오답이 떴던 쿼리.
여기서 shifts CTE에 해당하는 로직만
다시 수정해 보기로 했다.

WITH overlap
     AS (SELECT e1.employee_id,
                e1.start_time AS "previous_start",
                e1.end_time   AS "previous_end",
                e2.start_time,
                e2.end_time
         FROM   employeeshifts e1
                LEFT JOIN employeeshifts e2
                       ON e1.employee_id = e2.employee_id
         WHERE  e1.end_time > e2.start_time
                AND e1.start_time < e2.start_time),
     shifts
     AS (SELECT e.employee_id,
                Count(DISTINCT e.start_time) AS "max_overlapping_shifts"
         FROM   employeeshifts e
                LEFT JOIN overlap o
                       ON e.employee_id = o.employee_id
                          AND e.start_time = o.previous_start
                          AND e.end_time = o.previous_end
         GROUP  BY 1),
     duration
     AS (SELECT employee_id,
                Sum(Timestampdiff(minute, start_time, previous_end)) AS
                   "sum_of_duration"
         FROM   overlap
         GROUP  BY 1)
SELECT DISTINCT e.employee_id,
                max_overlapping_shifts,
                Ifnull(sum_of_duration, 0) AS "total_overlap_duration"
FROM   employeeshifts e
       LEFT JOIN shifts s
              ON e.employee_id = s.employee_id
       LEFT JOIN duration d
              ON e.employee_id = d.employee_id
ORDER  BY 1;

shifts를 구하기 전에 temp CTE를 하나 더 만들어서
각 employee_id별로 overlap된 근무 횟수를 시간대별로 각각 구하고,
shifts CTE에서는 employee별로 그 중 최대 cnt를 구하는 과정을 추가했다.

만들어진 지 얼마 안 된 최신 문제여서 그런지
정답 처리는 되었는데 runtime analysis 그래프는 보이지 않는
신기한 현상이 벌어졌다ㅎㅎ
나중에 다시 제출해 봐야지.

아래는 수정 제출한 정답 쿼리.

WITH overlap
     AS (SELECT e1.employee_id,
                e1.start_time AS "previous_start",
                e1.end_time   AS "previous_end",
                e2.start_time,
                e2.end_time
         FROM   employeeshifts e1
                LEFT JOIN employeeshifts e2
                       ON e1.employee_id = e2.employee_id
         WHERE  e1.end_time > e2.start_time
                AND e1.start_time < e2.start_time),
     temp
     AS (SELECT employee_id,
                previous_start,
                previous_end,
                Count(*) + 1 AS "cnt"
         FROM   overlap
         GROUP  BY 1,
                   2,
                   3),
     shifts
     AS (SELECT employee_id,
                Max(cnt) AS "shifts"
         FROM   temp
         GROUP  BY 1),
     duration
     AS (SELECT employee_id,
                Sum(Timestampdiff(minute, start_time, previous_end)) AS
                   "sum_of_duration"
         FROM   overlap
         GROUP  BY 1)
SELECT DISTINCT e.employee_id,
                Ifnull(shifts, 1)          AS "max_overlapping_shifts",
                Ifnull(sum_of_duration, 0) AS "total_overlap_duration"
FROM   employeeshifts e
       LEFT JOIN shifts s
              ON e.employee_id = s.employee_id
       LEFT JOIN duration d
              ON e.employee_id = d.employee_id
ORDER  BY 1;

오늘로 leetcode premium 문제 풀이도 끝났다.

앞으로는 너무 쉬운 문제만 제외하고
하루에 한 문제 정도만 리뷰하면서,
실제로 코딩테스트에서 비슷한 문제를 마주쳤을 때
어떤 식으로 생각해서 풀면 될지를 자세하게 정리해 볼 예정.