241207_TIL

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

SQL 문제풀이 복습

---

Leetcode

문제 링크
recursive cte를 써야 해서 약간 까다로운 문제.
이 문제의 킥은

1. cte에서 week_no를 만들 때 row_number() over()를 쓸 줄 알아야 하는 점. over() 안에 partition by나 order by를 쓰지 않고도 그냥 row_number가 1부터 자동으로 생성된다.

2. date_add(DATE, interval 7 day)를 쓸 줄 알아야 하는 점. 이 때 7 days가 아니라 그냥 7 day다. 이걸 몰라서 계속 오류로 머리를 싸매고 있었(...)

cte만 잘 만들 수 있으면
그 뒤부터는 join만 하면 돼서 크게 어렵지 않다.

WITH recursive all_friday
AS
  (
         SELECT row_number() over() AS "week_no",
                '2023-11-03'        AS "purchase_date"
         UNION ALL
         SELECT week_no+1,
                date_add(purchase_date, INTERVAL 7 DAY)
         FROM   all_friday
         WHERE  purchase_date <= '2023-11-17')
  SELECT   a.week_no AS "week_of_month",
           p.purchase_date,
           SUM(amount_spend) AS "total_amount"
  FROM     Purchases p
  JOIN     all_friday a
  ON       p.purchase_date = a.purchase_date
  GROUP BY 1
  ORDER BY 1;

문제 링크
위의 문제와 cte까지는 완전히 똑같은데,

1. amount_spend가 없는 주는 0으로 표기해야 하므로
   inner가 아니라 left join으로 변경해야 하고,

2. join의 순서도 (inner가 아니므로) cte를 기준으로 두고
   Puchases를 left join하는 식으로 변경해야 한다.

WITH recursive all_friday
AS
  (
         SELECT row_number() over() AS "week_no",
                '2023-11-03'        AS "purchase_date"
         UNION ALL
         SELECT week_no+1,
                date_add(purchase_date, INTERVAL 7 DAY)
         FROM   all_friday
         WHERE  purchase_date <= '2023-11-17')
  SELECT    a.week_no AS "week_of_month",
            a.purchase_date,
            ifnull(SUM(amount_spend),0) AS "total_amount"
  FROM      all_friday a
  LEFT JOIN Purchases p
  ON        p.purchase_date = a.purchase_date
  GROUP BY  1
  ORDER BY  1;

문제 링크
단계별로 나눠서 해결하면 어렵지 않다.

1. rank()를 써서 세션 순서별로 번호를 매긴다.

2. 1번 세션(가장 빠른)의 session_type이 Viewer인 유저의 user_id를 뽑는다.

3. 그걸 다시 Sessions 테이블과 inner join해서 2번에서 뽑은 user_id의 데이터만 남긴다.

4. 이제 session_type이 Streamer인 경우만 추려서 가짓수를 세면 끝. (이미 첫 세션이 Viewer인 것은 2번까지의 과정을 통해 식별되어 있고, 해당하는 user_id만 넘어온 상태이므로)

SELECT s.user_id,
       Count(session_id) AS "sessions_count"
FROM   Sessions s
       JOIN (SELECT user_id
             FROM   (SELECT user_id,
                            session_start,
                            session_type,
                            RANK()
                              OVER(
                                partition BY user_id
                                ORDER BY session_start) AS "ranking"
                     FROM   Sessions) a
             WHERE  session_type = 'Viewer'
                    AND ranking = '1') b
         ON s.user_id = b.user_id
WHERE  session_type = 'Streamer'
GROUP  BY 1
ORDER  BY 2 DESC,
          1 DESC;

컨디션이 회복되니 medium, hard 문제로만 풀었는데도
시간이 오래 걸리지 않네. 컨디션 관리 잘 하자 앞으로도!