241202_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
이동평균 구하기

1. user_id별로 steps_date를 하나씩 미룬 열(step1), 두 개씩 미룬 열(step2)을 추가한다. 나중에 날짜 차이가 딱 하루씩 나는지 연산할 기준이 필요하기 때문.

2. 이동평균을 구해준다. window함수 중 avg()를 쓰면 되는데, 해당 행을 포함해 2개 행 이전까지를 구해야 하므로 rows between 2 preceding and current row로 써 준다. 이걸 못 떠올리면 그냥 못 푸는 문제.

3. 1~2의 결과를 서브쿼리로 처리한 다음, where절을 써서 날짜 차이가 딱 하루씩 나는지를 확인하고 해당하는 경우만 user_id, steps_date, rolling_average를 불러온다.

SELECT user_id,
       steps_date,
       rolling_average
FROM   (SELECT user_id,
               steps_date,
               LAG(steps_date)
                 over(
                   PARTITION BY user_id
                   ORDER BY steps_date)
                      AS "step1",
               LAG(steps_date, 2)
                 over(
                   PARTITION BY user_id
                   ORDER BY steps_date)
                      AS "step2",
               ROUND(AVG(steps_count)
                       over(
                         PARTITION BY user_id
                         ORDER BY steps_date ROWS BETWEEN 2 preceding AND
                       CURRENT ROW),
               2) AS
               "rolling_average"
        FROM   Steps) result
WHERE  DATEDIFF(steps_date, step1) = 1
       AND DATEDIFF(step1, step2) = 1
ORDER  BY 1,
          2;

문제 링크
매 6분마다 그루핑의 기준을 다르게 적용해야 하므로
minute을 6으로 나눈 값을 ceil, 즉 올림 처리하면
group_id를 구할 수 있다. 이걸 기준으로 sum하면 끝.

※ minute 컬럼이 어디까지 이어질지 모르기 때문에,
case when을 써서 1~6분은 1, 7~12분은 2, ...
이런 식으로 쓰면 답을 구할 수 없음.
일반화시킬 수 있는 규칙을 만드는 것이 필요함.

SELECT interval_no,
       SUM(order_count) AS "total_orders"
FROM   (SELECT minute,
               CEIL(minute / 6) AS "interval_no",
               order_count
        FROM   Orders) result
GROUP  BY 1
ORDER  BY 1;

문제 링크

WITH result
     AS (SELECT o.seller_id,
                Count(DISTINCT o.item_id) AS "num_items"
         FROM   Orders o
                JOIN Users u
                  ON o.seller_id = u.seller_id
                JOIN Items i
                  ON o.item_id = i.item_id
         WHERE  favorite_brand <> item_brand
         GROUP  BY 1)
SELECT seller_id,
       num_items
FROM   result
WHERE  num_items = (SELECT Max(num_items)
                    FROM   result)
ORDER  BY 1;