240825_TIL

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

SQL 코드카타

문제 링크

1. 원본 데이터에 row_id를 하나씩 생성한다.
   이 때 순서가 꼬이지 않게 partition이나 order by 없이
   그냥 row_number만 쓰는 게 중요. (lead같은 window함수를 써도 될 듯.)
2. 이렇게 row_number까지 붙인 CTE a로부터,
   start와 stop에 해당하는 CTE를 각각 만든다.
3. 이제 본 쿼리에 들어와서 start와 stop 테이블을 조인하되,
   조인의 조건은 server_id가 동일할 것 + row_id의 차이가 1일 것
   이렇게 두 가지다. 이 때 그냥 row_id끼리 뺄셈을 하는 건 안 되고
   cast를 써서 연산이 가능하게 바꿔줘야 하는 점에 주의.
4. 조인이 끝나고 나면 모든 timestampdiff를 다 더한 다음,
   60, 60, 24로 나누어서 일수로 바꿔주고 내림처리를 위해 floor를 씌워주면 끝.

WITH a
     AS (SELECT *,
                Row_number()
                  OVER() AS "row_id"
         FROM   servers),
     start
     AS (SELECT *
         FROM   a
         WHERE  session_status = 'start'),
     stop
     AS (SELECT *
         FROM   a
         WHERE  session_status = 'stop')
SELECT Floor(
Sum(Timestampdiff(second, start.status_time, stop.status_time)) / 60 / 60 / 24)
AS "total_uptime_days"
FROM   stop
       LEFT JOIN start
              ON stop.server_id = start.server_id
                 AND Cast(stop.row_id AS SIGNED) - Cast(start.row_id AS SIGNED)
                     = 1;

문제 링크
크게 어렵지는 않은 문제.
group by의 기준이 될 열을 만들어주기 위해
row_id를 CTE에서 생성해 주는 것 외에는 크게 신경쓸 게 없다.

똑같은 갯수의 조합이 여러 개 나올 수도 있으므로
result에서 끝을 내는 게 아니라 한 번 더 CTE로 뺀 다음에
본 쿼리의 where절에서 최대값 조건을 거는 것 정도?

WITH a
     AS (SELECT seat_id,
                free,
                Row_number()
                  OVER(
                    ORDER BY seat_id) AS "row_id"
         FROM   cinema
         WHERE  free = 1),
     result
     AS (SELECT Min(seat_id)                    AS "first_seat_id",
                Max(seat_id)                    AS "last_seat_id",
                Max(seat_id) - Min(seat_id) + 1 AS "consecutive_seats_len"
         FROM   a
         GROUP  BY seat_id - row_id
         ORDER  BY 1)
SELECT *
FROM   result
WHERE  consecutive_seats_len = (SELECT Max(consecutive_seats_len)
                                FROM   result)
ORDER  BY 1;

문제 링크
어떤 문자열의 갯수를 구할 때는
length에서 char_length를 빼면 된다.

이 때 char_length를 구할 때 replace를 써서
해당 문자를 공백으로 바꾼 후에 빼 줘야 한다.
즉, (온전한 문자열 길이 - 해당 문자가 없을 경우의 문자열 길이)
= 해당 문자가 쓰인 횟수
인 셈.

SELECT tweet_id
FROM   tweets
WHERE  Length(content) - Char_length(REPLACE(content, '@', '')) > 3
        OR Length(content) - Char_length(REPLACE(content, '#', '')) > 3
        OR Char_length(content) > 140
ORDER  BY 1;

문제 링크
크게 세 파트로 나누어서 해결.

1. 각 employee의 전체 task 시간을 합치는 단계 (CTE a,b)
2. employee별로 중첩되는 시간을 구하는 단계 (CTE overlapped)
3. employee별로 동시에 쳐내고 있는 일의 숫자를 구하는 단계 (CTE concurrent)
   • 이 단계가 가장 어려웠다. join을 어떻게 하느냐가 관건.

이 3단계를 모두 구할 수 있다면
그 이후 본 쿼리에서의 연산은 간단하다.

WITH a
     AS (SELECT task_id,
                employee_id,
                start_time,
                end_time,
                Timestampdiff(minute, start_time, end_time) AS "agg_minute"
         FROM   tasks),
     b
     AS (SELECT employee_id,
                Sum(agg_minute) AS "agg"
         FROM   a
         GROUP  BY 1),
     overlapped
     AS (SELECT t1.employee_id,
                Sum(Timestampdiff(minute, t2.start_time, t1.end_time)) AS
                "overlap"
         FROM   tasks t1
                INNER JOIN tasks t2
                        ON t1.employee_id = t2.employee_id
                           AND t2.start_time > t1.start_time
                           AND t2.start_time < t1.end_time
         GROUP  BY 1),
     concurrent
     AS (SELECT t1.employee_id,
                Count(t2.task_id) AS "concurrent"
         FROM   tasks t1
                INNER JOIN tasks t2
                        ON t1.employee_id = t2.employee_id
                           AND t1.start_time <= t2.start_time
                           AND t2.start_time < t1.end_time
         GROUP  BY t1.employee_id,
                   t1.task_id)
SELECT b.employee_id,
       Floor(( agg - Ifnull(overlap, 0) ) / 60) AS "total_task_hours",
       Max(c.concurrent)                        AS "max_concurrent_tasks"
FROM   b
       LEFT JOIN overlapped o
              ON b.employee_id = o.employee_id
       LEFT JOIN concurrent c
              ON b.employee_id = c.employee_id
GROUP  BY 1
ORDER  BY 1;

문제 링크
크게 어렵지 않은 문제.

WITH a
     AS (SELECT car_id,
                Sum(fee_paid) AS "total_fee_paid",
                Round(Sum(fee_paid) / ( Sum(Timestampdiff(minute, entry_time,
                                            exit_time)
                                        ) / 60 )
                , 2)          AS "avg_hourly_fee"
         FROM   parkingtransactions
         GROUP  BY 1),
     b
     AS (SELECT car_id,
                lot_id,
                Sum(Timestampdiff(minute, entry_time, exit_time)) AS "time_lot"
         FROM   parkingtransactions
         GROUP  BY 1,
                   2),
     c
     AS (SELECT car_id,
                lot_id AS "most_time_lot"
         FROM   b
         WHERE  time_lot IN (SELECT Max(time_lot)
                             FROM   b
                             GROUP  BY car_id)
         GROUP  BY 1)
SELECT a.car_id,
       a.total_fee_paid,
       a.avg_hourly_fee,
       c.most_time_lot
FROM   a
       JOIN c
         ON a.car_id = c.car_id
ORDER  BY 1;

오늘 문제풀이는 이걸로 끝!