241214_TIL

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

SQL 문제풀이 복습

---

Leetcode

문제 링크
membership이 비어있거나
week_of_month가 비어있는 경우가 있을 수 있어서
cross join과 left join을 잘 섞어서 써야 한다.
그냥 (inner) join으로만 해결하면 오류가 생기는 부분이 있음.

맨 처음 recursive cte를 만들 때에
date_add + interval을 쓰는 것도 생각해낼 수 있어야 함.

WITH recursive friday
AS
  (
         SELECT row_number() over() AS "row_num",
                '2023-11-03'        AS "pdate"
         UNION ALL
         SELECT row_num+1,
                date_add(pdate, INTERVAL 7 DAY)
         FROM   friday
         WHERE  pdate <= '2023-11-17'),
  result
AS
  (
            SELECT    u.membership,
                      purchase_date,
                      SUM(amount_spend) AS "total_amount"
            FROM      Users u
            LEFT JOIN Purchases p
            ON        u.user_id = p.user_id
            WHERE     MEMBERSHIP IN ('Premium',
                                     'VIP')
            GROUP BY  1,
                      2),
  temp
AS
  (
                  SELECT DISTINCT row_num AS "week_of_month",
                                  MEMBERSHIP,
                                  pdate
                  FROM            friday,
                                  result
                  ORDER BY        1,
                                  2)
  SELECT    t.week_of_month,
            t.membership,
            ifnull(total_amount,0) AS "total_amount"
  FROM      temp t
  LEFT JOIN result r
  ON        t.pdate = r.purchase_date
  AND       t.membership = r.membership
  ORDER BY  1,
            2;

문제 링크
sec_to_time은 이 문제에서 썼던 걸 제외하면
다른 문제에서는 거의 못 봤던 함수다.
그래도 혹시 모르니 기억은 해 두고 있자.
date_format을 썼을 때 %T로 형식을 지정하면 HH:MM:SS처럼 나온다는 것도.

SELECT first_name,
       type,
       DATE_FORMAT(SEC_TO_TIME(duration), '%T') AS "duration_formatted"
FROM   (SELECT c.id,
               first_name,
               type,
               duration,
               RANK()
                 OVER(
                   partition BY type
                   ORDER BY duration DESC) AS "ranking"
        FROM   Calls cl
               JOIN Contacts c
                 ON cl.contact_id = c.id) result
WHERE  ranking <= 3
ORDER  BY 2 DESC,
          3 DESC,
          1 DESC;

문제 링크
1트(8/25) 때와는 꽤 다른 방법으로 풀었다.

start와 stop을 기준으로 테이블을 2개 만들어서
server_id와 row_id가 같음을 기준으로 join하고 한 번에 연산으로 해결했는데,
이렇게 하면 cte를 만들지 않고도 풀이가 가능하다.
(정확한 집계를 위해 second 기준으로 합산한 뒤 60/24/24/ 연산은 필수)

status_time이 겹칠 일이 없기 때문에
start와 stop의 row_id를 따로따로 매겨도
join의 조건으로 쓸 수 있는 것.

SELECT Floor(Sum(TIMESTAMPDIFF(second, start_time, stop_time)) / 60 / 60 / 24)
       AS
       "total_uptime_days"
FROM   (SELECT server_id,
               status_time               AS "start_time",
               ROW_NUMBER()
                 OVER(
                   partition BY server_id
                   ORDER BY status_time) AS "row_id"
        FROM   Servers
        WHERE  session_status = 'start') start
       JOIN (SELECT server_id,
                    status_time               AS "stop_time",
                    ROW_NUMBER()
                      OVER(
                        partition BY server_id
                        ORDER BY status_time) AS "row_id"
             FROM   Servers
             WHERE  session_status = 'stop') stop
         ON start.server_id = stop.server_id
            AND start.row_id = stop.row_id;

다른 사람의 풀이를 찾아보니
이런 개쩌는 한줄짜리 풀이도 있긴 했다.

SELECT FLOOR(SUM(CASE
                   WHEN session_status = 'start' THEN -
                   UNIX_TIMESTAMP(status_time)
                   ELSE UNIX_TIMESTAMP(status_time)
                 end) / ( 60 * 60 * 24 )) AS "total_uptime_days"
FROM   Servers;

정확하게는 모르겠지만
unix_timestamp라는 함수로 status_time을 감싸면
timestampdiff같은 형태가 아니어도 그냥 바로 연산이 가능한 것 같다.
이걸 알고 있었으면 나도 한 줄 풀이로 해결할 수 있었을까..?