240810_TIL

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

SQL 코드카타

문제 링크
문제에서 올림을 허용한다고 했던 게 힌트.
timestampdiff 함수를 쓸 때 인자를 minute으로 쓰면
단위가 뭉개지므로, 애매한 케이스인 2번 employee를
제대로 발라낼 수 없게 된다.
(총 시간이 719분이어도 12시간, 720분이어도 12시간이 되어야 함)

따라서 먼저 second 단위로 구하고
분으로 변환하기 위해 60으로 나누되,
이 때 CEIL 함수를 적용해 올림 처리하면
조건에 맞게 구할 수 있다.

WITH a
     AS (SELECT employee_id,
                Sum(Ceil(Timestampdiff(second, in_time, out_time) / 60)) AS
                "total"
         FROM   logs
         GROUP  BY 1)
SELECT e.employee_id
FROM   employees e
       LEFT JOIN a
              ON e.employee_id = a.employee_id
WHERE  e.needed_hours * 60 > Ifnull(total, 0);

문제 링크
leetcode hard의 정석 조합이 나왔다.

1. recursive CTE
2. window함수
3. 당연히 join

이번 문제에서는 year도 연속으로 올라가야 하고
price도 그에 맞게 따라서 올라가는 케이스를 뽑아야 한다.
이걸 rank함수를 써서 하나씩 빼고 있으면
가짓수가 몇 개인지 알 수가 없어서 오류 케이스 대응이 어렵다.

따라서, customer_id별로
max(year)에서 min(year)를 뺀 값(총 년도의 수),
그리고 lead 함수를 써서 price를 한 해씩 밀어놓은 다음
연속해서 증가하는 경우의 수를 count한 결과를 비교해
두 수가 일치하는 경우의 customer_id를 뽑으면 된다.
그러면 year도 계속 증가하고, 그에 맞춰서 price도
계속 증가한 customer_id만 나오게 된다.

이 문제는 나중에 꼭 다시 풀어보기.

WITH recursive p_year
AS
  (
           SELECT   customer_id,
                    min(year(order_date)) AS "minyear",
                    max(year(order_date)) AS "maxyear"
           FROM     orders
           GROUP BY 1),
  total_year
AS
  (
         SELECT customer_id,
                minyear AS "min"
         FROM   p_year
         UNION ALL
         SELECT ty.customer_id,
                ty.min + 1
         FROM   total_year ty
         JOIN   p_year py
         ON     ty.customer_id = py.customer_id
         AND    ty.min < py.maxyear),
  base
AS
  (
           SELECT   customer_id,
                    year(order_date) AS "year",
                    sum(price)       AS "total_purchase"
           FROM     orders
           GROUP BY 1,
                    2),
  lead1
AS
  (
           SELECT   *,
                    lead(total_purchase) over(partition BY customer_id ORDER BY year) AS "lead1"
           FROM     base),
  result
AS
  (
           SELECT   customer_id,
                    count(DISTINCT
                    CASE
                             WHEN lead1 > total_purchase THEN year
                             ELSE NULL
                    end)                AS "times",
                    max(year)-min(year) AS "year_present"
           FROM     lead1
           GROUP BY 1)
  SELECT customer_id
  FROM   result
  WHERE  times = year_present;

문제 링크
간단한 cross join 문제.

SELECT a.symbol AS metal,
       b.symbol AS nonmetal
FROM   elements AS a,
       elements AS b
WHERE  a.type = "metal"
       AND b.type = "nonmetal"; 

최종발표 준비를 해야 하니
오늘은 3문제만!