240720_TIL

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

SQL 코드카타

문제 링크
재귀CTE를 써서 간단하게 해결한 문제.

예전에는 recursive를 쓰기 시작하면 머리가 복잡해졌는데
이제는 제법 막히지 않고 짜 보는 정도까진 올라온 것 같다.
속도 × 정확도까지 잘 챙겨보자.

WITH recursive total_id
AS
  (
         SELECT 1 AS "id"
         FROM   customers
         UNION
         SELECT id+1
         FROM   total_id
         WHERE  id <
                (
                       SELECT max(customer_id)
                       FROM   customers))
  SELECT    t.id AS "ids"
  FROM      total_id t
  LEFT JOIN customers c
  ON        t.id = c.customer_id
  WHERE     c.customer_id IS NULL;

문제 링크
cross join을 해서 모든 경우의 수를 다 뽑은 다음에
where절에 조건을 채워넣으면 간단히 구할 수 있음.

SELECT DISTINCT schoola.student_name AS "member_A",
                schoolb.student_name AS "member_B",
                schoolc.student_name AS "member_C"
FROM   schoola,
       schoolb,
       schoolc
WHERE  schoola.student_id <> schoolb.student_id
       AND schoolb.student_id <> schoolc.student_id
       AND schoola.student_id <> schoolc.student_id
       AND schoola.student_name <> schoolb.student_name
       AND schoolb.student_name <> schoolc.student_name
       AND schoola.student_name <> schoolc.student_name; 

문제 링크
아무리 난이도 hard여도
CTE를 5개 이상 만들어야 풀리는 경우는 별로 없는 듯하다.
※ 난이도 hard = 재귀적CTE + CTE 4~5개 + window함수 조합

이 문제의 킥은 recursive all_month를 만들 때,
2020년 1월 이전의 데이터가 있느냐 vs 2020년 1월 데이터가 아예 없느냐
에 따라 결과값이 달라진다는 것.

만약 Drivers 테이블 데이터가 2020년 2월부터 시작이라면,
2020년 1월 데이터를 넣어주고 값을 0으로 만들어야 하므로
최초의 all_month CTE 안에 case when 구문이 들어가야 한다.

그 외에 sum() over 등의 window함수를 쓰는 부분은 크게 어렵지 않다.
b CTE를 만들 때 where절을 걸어버리면 sum() over의 연산결과가 달라지기 때문에,
b까지는 만들어 두고 c를 새로 만들어서 b의 결과를 받을 때
where 조건을 넣어야 한다, 는 것 정도만 주의하면 될 듯.

WITH recursive all_month
AS
  (
         SELECT
                CASE
                       WHEN min(join_date) >= '2020-01-01' THEN '2020-01-01'
                       ELSE min(join_date)
                end AS "join_month"
         FROM   drivers
         UNION ALL
         SELECT date_add(join_month,INTERVAL 1 month)
         FROM   all_month
         WHERE  join_month < '2020-11-30'),
  a
AS
  (
            SELECT    a.join_month,
                      count(d.driver_id) AS "cnt"
            FROM      all_month a
            LEFT JOIN drivers d
            ON        date_format(a.join_month,'%Y-%m') = date_format(d.join_date,'%Y-%m')
            GROUP BY  1),
  b
AS
  (
           SELECT   join_month,
                    sum(cnt) over (ORDER BY join_month) AS "active_drivers"
           FROM     a),
  c
AS
  (
         SELECT month(join_month) AS "month",
                active_drivers
         FROM   b
         WHERE  year(join_month) = '2020'),
  d
AS
  (
             SELECT     month(requested_at) AS "ride_month",
                        count(r.ride_id)    AS "accepted_rides"
             FROM       rides r
             INNER JOIN acceptedrides ar
             ON         r.ride_id = ar.ride_id
             WHERE      year(requested_at) = '2020'
             GROUP BY   1)
  SELECT    month,
            active_drivers,
            ifnull(accepted_rides,0) AS "accepted_rides"
  FROM      c
  LEFT JOIN d
  ON        c.month = d.ride_month;

문제 링크

SELECT p.name,
       Ifnull(Sum(rest), 0)     AS "rest",
       Ifnull(Sum(paid), 0)     AS "paid",
       Ifnull(Sum(canceled), 0) AS "canceled",
       Ifnull(Sum(refunded), 0) AS "refunded"
FROM   product p
       LEFT JOIN invoice i
              ON p.product_id = i.product_id
GROUP  BY 1
ORDER  BY 1;

문제 링크

WITH result
     AS (SELECT Sum(b.apple_count)  AS "apple_count",
                Sum(b.orange_count) AS "orange_count"
         FROM   boxes b
                LEFT JOIN chests c
                       ON b.chest_id = c.chest_id
         UNION
         SELECT Sum(c.apple_count),
                Sum(c.orange_count)
         FROM   boxes b
                LEFT JOIN chests c
                       ON b.chest_id = c.chest_id)
SELECT Sum(apple_count)  AS "apple_count",
       Sum(orange_count) AS "orange_count"
FROM   result;