241122_TIL

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

SQL 문제풀이 복습

여행 다녀오느라 처음으로 포스팅을 사흘 이상 못 했다.
오늘부터 다시 꾸준히 이어가 보기🔥

---

Leetcode

문제 링크
window함수 중 first_value를 활용해야 하는 문제.
partition by 뒤에 있는 값을 기준으로,
해당 partition의 첫 번째 값을 불러오는 함수다.

이 first_value 함수가 잘 작동하려면
그 이전에 서브쿼리를 만드는 단계에서 order by를 잘 해줘야 함.
자세한 내용은 1트의 풀이를 참고할 것.

SELECT id,
       FIRST_VALUE(drink)
         OVER(
           PARTITION BY group_id
           ORDER BY row_id) AS "drink"
FROM   (SELECT id,
               drink,
               row_id,
               SUM(IF(drink IS NULL, 0, 1))
                 OVER(
                   ORDER BY row_id) AS "group_id"
        FROM   (SELECT id,
                       drink,
                       ROW_NUMBER()
                         OVER() AS "row_id"
                FROM   CoffeeShop) a) b;

문제 링크
시간 계산하는 timestampdiff 함수를 잘 써야 하는 문제.

timestampdiff의 인자를 그냥 minute이나 hour로 쓰면
애매한 시간대들이 제대로 계산되지 않는 문제가 생긴다.
따라서 second로 계산하고 60으로 나눠 분으로 변환하되,
일괄적으로 올림 처리하기 위해 ceil + sum 함수를 써서
total_worktime을 구해야 한다.

개념을 아냐, 모르냐보다는 센스가 중요한 문제인데,
이 부분을 떠올리지 못하면 푸는 데 상당히 시간이 오래 걸렸을 것 같다.
풀이 참고

SELECT e.employee_id
FROM   Employees e
       LEFT JOIN (SELECT employee_id,
                         SUM(CEIL(TIMESTAMPDIFF(SECOND, in_time, out_time) / 60)
                         ) AS
                                                      "total_worktime"
                  FROM   Logs
                  GROUP  BY 1) a
              ON e.employee_id = a.employee_id
WHERE  needed_hours * 60 > IFNULL(total_worktime, 0);

문제 링크
recursive cte를 써서
'각 customer_id별로 빠지는 해 없이 년도를 채워주는'
쿼리를 만드는 게 가장 어려운 부분이다.
recursive cte를 만들고 추가로 join까지 해야 해서 더 어렵게 느껴진 듯.

일단 cte를 완성하고 나면
그 뒤로는 customer_id별로 년도별 total_purchase를 구한 다음
year와 total_purchase 각각에 rank를 적용하고,
rank_year와 rank_purchase가 일치하지 않는,
즉 해가 거듭될수록 구매량이 지속적으로 늘어나지 못하는 customer_id는
제외하고 나머지 customer_id를 찾아주면 된다.

recursive cte + window함수 + join과 그루핑 등등
Leetcode에서 나올 수 있는 가장 어려운 조합이 들어간 좋은 문제.
풀이 참고

WITH recursive cte
AS
  (
           SELECT   customer_id,
                    YEAR(MIN(order_date)) AS "minyear",
                    YEAR(MAX(order_date)) AS "maxyear"
           FROM     Orders
           GROUP BY 1),
  total_year
AS
  (
         SELECT customer_id,
                minyear
         FROM   cte
         UNION ALL
         SELECT ty.customer_id,
                ty.minyear + 1
         FROM   total_year ty
         JOIN   cte
         ON     ty.customer_id = cte.customer_id
         AND    ty.minyear < cte.maxyear),
  result
AS
  (
            SELECT    ty.customer_id,
                      minyear                  AS "year",
                      ifnull(total_purchase,0) AS "total_purchase"
            FROM      total_year ty
            LEFT JOIN
                      (
                               SELECT   customer_id,
                                        YEAR(order_date) AS "order_year",
                                        SUM(price)       AS "total_purchase"
                               FROM     Orders
                               GROUP BY 1,
                                        2) a
            ON        ty.customer_id = a.customer_id
            AND       ty.minyear = a.order_year),
  final_result
AS
  (
           SELECT   customer_id,
                    YEAR,
                    total_purchase,
                    rank() over(partition BY customer_id ORDER BY YEAR)           AS "rank_year",
                    rank() over(partition BY customer_id ORDER BY total_purchase) AS "rank_purchase"
           FROM     result
           ORDER BY 1,
                    2)
  SELECT DISTINCT customer_id
  FROM            final_result
  WHERE           customer_id NOT IN
                  (
                         SELECT customer_id
                         FROM   final_result
                         WHERE  rank_year <> rank_purchase);