241017_TIL

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

SQL 문제풀이 복습

---

Leetcode

문제 링크
서브쿼리 활용 문제.

SELECT contest_id,
       Round(100.0 * Count(DISTINCT user_id) / (SELECT Count(*)
                                                FROM   users), 2) AS
       "percentage"
FROM   Register
GROUP  BY 1
ORDER  BY 2 DESC,
          1 ASC;

문제 링크
1트(7/20)에는 cte를 여러 개 만들어가며 풀었지만
이번에는 recursive CTE 한 개만 만들고 나머지는 서브쿼리로 처리했다.
서브쿼리의 숫자를 줄일 수 있었던 포인트는
2020년 전에 join되어 있던 driver를 전부 2020-01로 합산해 주는 것.
(인라인뷰 a의 case when 구문)

인라인뷰 b는 상대적으로 간단하게 만들 수 있고,
완성된 recursive cte와 a, b를 join으로 연결해 주면 끝.
active_drivers에 null이 뜰 수도 있으므로 ifnull 처리해 주는 것만 조심.

WITH recursive cte
AS
  (
         SELECT 1 AS "month"
         UNION ALL
         SELECT MONTH+1
         FROM   cte
         WHERE  MONTH < 12)
  SELECT    cte.month,
            ifnull(SUM(cnt) over(ORDER BY MONTH),0) AS "active_drivers",
            ifnull(cnt_accepted,0)                  AS "accepted_rides"
  FROM      cte
  LEFT JOIN
            (
                     SELECT
                              CASE
                                       WHEN YEAR(join_date) < '2020' THEN '2020-01'
                                       ELSE date_format(join_date,'%Y-%m')
                              END              AS "ym",
                              COUNT(driver_id) AS "cnt"
                     FROM     Drivers
                     WHERE    YEAR(join_date) <= '2020'
                     GROUP BY 1) a
  ON        cte.month = RIGHT(ym,2)
  LEFT JOIN
            (
                     SELECT   MONTH(requested_at) AS "accepted_month",
                              COUNT(r.ride_id)    AS "cnt_accepted"
                     FROM     Rides r
                     JOIN     AcceptedRides a
                     ON       r.ride_id = a.ride_id
                     WHERE    YEAR(requested_at) = '2020'
                     GROUP BY 1) b
  ON        cte.month = b.accepted_month
  ORDER BY  1;

문제 링크
이것도 비슷한 hopper company 문제.
중간에 오류가 생겨서 쿼리 일부를 점검하느라
원래는 서브쿼리 형태로 만들었던 것들을 cte로 바꿨다. (a, b)

문제에서 구하라고 한 것이 driver의 숫자와 관련된 거였는데
cte b에서 driver_id가 아니라 ride_id를 세는 바람에
분모가 1씩 늘어나서 계산이 틀어졌던 문제가 있었음.

그 외에 recursive cte만드는 요령이나
구하려는 테이블 각각을 만들어서 join하는 방식 등은 달라지지 않음.

WITH recursive cte
AS
  (
         SELECT 1 AS "month"
         UNION ALL
         SELECT MONTH+1
         FROM   cte
         WHERE  MONTH < 12),
  a
AS
  (
           SELECT
                    CASE
                             WHEN join_date < '2020-01-01' THEN '2020-01'
                             ELSE date_format(join_date,'%Y-%m')
                    END              AS "join_month",
                    COUNT(driver_id) AS "cnt"
           FROM     Drivers
           WHERE    YEAR(join_date) <= '2020'
           GROUP BY 1
           ORDER BY 1),
  b
AS
  (
           SELECT   MONTH(requested_at)         AS "acc_month",
                    COUNT(DISTINCT a.driver_id) AS "accepted_rides"
           FROM     Rides r
           JOIN     AcceptedRides a
           ON       r.ride_id = a.ride_id
           WHERE    YEAR(requested_at) = '2020'
           GROUP BY 1)
  SELECT    MONTH,
            ifnull(ROUND(100.0*accepted_rides/SUM(cnt) over(ORDER BY MONTH),2),0) AS "working_percentage"
  FROM      cte
  LEFT JOIN a
  ON        cte.month = RIGHT(a.join_month,2)
  LEFT JOIN b
  ON        cte.month = b.acc_month;

---

프로그래머스

문제 링크
간단한 서브쿼리와 join, group by 문제.

SELECT y                            AS "YEAR",
       max_of_year - SIZE_OF_COLONY AS "YEAR_DEV",
       id
FROM   Ecoli_data e
       JOIN (SELECT Year(DIFFERENTIATION_DATE) AS "y",
                    Max(SIZE_OF_COLONY)        AS "max_of_year"
             FROM   Ecoli_data
             GROUP  BY 1) a
         ON Year(e.DIFFERENTIATION_DATE) = a.y
ORDER  BY 1,
          2;

문제 링크

SELECT CASE
         WHEN month(DIFFERENTIATION_DATE) <= 3 THEN '1Q'
         WHEN month(DIFFERENTIATION_DATE) <= 6 THEN '2Q'
         WHEN month(DIFFERENTIATION_DATE) <= 9 THEN '3Q'
         ELSE '4Q'
       end       AS "QUARTER",
       count(id) AS "ECOLI_COUNT"
FROM   ECOLI_DATA
GROUP  BY 1
ORDER  BY 1;

문제 링크
위의 문제랑 난이도 차이가 거의 없는데 무슨 기준일까🤔

SELECT id,
       CASE
         WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
         WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
         ELSE 'HIGH'
       end AS "SIZE"
FROM   ECOLI_DATA
ORDER  BY 1;

문제 링크
join과 집계함수(count), ifnull의 조합 문제.

SELECT e1.id,
       ifnull(count(DISTINCT e2.id), 0) AS "CHILD_COUNT"
FROM   ECOLI_DATA e1
       LEFT JOIN ECOLI_DATA e2
              ON e1.id = e2.parent_id
GROUP  BY 1
ORDER  BY 1;

문제 링크
group by에 having절을 넣어서 조건을 주는 문제.
LENGTH가 null일 경우 10으로 계산하라고 한 점도 잘 살펴보기.

SELECT count(*)    AS "FISH_COUNT",
       max(LENGTH) AS "MAX_LENGTH",
       FISH_TYPE
FROM   FISH_INFO
GROUP  BY 3
HAVING avg(ifnull(LENGTH, 10)) >= 33
ORDER  BY 3;