240818_TIL

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

SQL 코드카타

문제 링크
이동평균rolling average을 구하는 문제.
예전에도 비슷한 문제를 코드카타에서 다뤘던 적이 있었다.

먼저 아래와 같은 CTE를 만들어 준다.
이동평균까지 한꺼번에 구한 버전이고,
1stepbefore, 2stepbefore는 본 쿼리에서
사흘이 연속되는지를 검증하기 위해 집어넣은 계산용 컬럼이다.

WITH result
AS
  (
           SELECT   user_id,
                    steps_date,
                    round(avg(steps_count) over(partition BY user_id ORDER BY steps_date rows BETWEEN 2 preceding AND      current row),2) AS "rolling_average",
                    lag(steps_date) over(partition BY user_id ORDER BY steps_date)                                                         AS "1stepbefore",
                    lag(steps_date,2) over(partition BY user_id ORDER BY steps_date)                                                       AS "2stepbefore"
           FROM     steps)

이제 이 result CTE로부터 본 쿼리를 작성한다.

앞에서 구한 1stepbefore와 2stepbefore를 where 절에 넣어서
날짜 차이가 정확히 하루씩 나는지,
즉 steps_date까지 포함해 사흘간의 이동평균인지를
보장하는 조건을 추가해 주면 완성.

WITH result
AS
  (
           SELECT   user_id,
                    steps_date,
                    round(avg(steps_count) over(partition BY user_id ORDER BY steps_date rows BETWEEN 2 preceding AND      current row),2) AS "rolling_average",
                    lag(steps_date) over(partition BY user_id ORDER BY steps_date)                                                         AS "1stepbefore",
                    lag(steps_date,2) over(partition BY user_id ORDER BY steps_date)                                                       AS "2stepbefore"
           FROM     steps)
  SELECT   user_id,
           steps_date,
           rolling_average
  FROM     result
  WHERE    datediff(steps_date,1stepbefore) = 1
  AND      datediff(1stepbefore,2stepbefore) = 1
  ORDER BY 1,
           2;

문제 링크

WITH result
     AS (SELECT o.seller_id,
                Count(DISTINCT o.item_id) AS "num_items"
         FROM   orders o
                LEFT JOIN users u
                       ON o.seller_id = u.seller_id
                LEFT 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;

문제 링크
전에도 비슷한 문제를 푼 적이 있는 것 같다.

self join으로 대칭되는 경우를 찾은 후,
cnt를 구해서 2 이상인 경우만 찾아야 한다.
(대칭되는 경우가 '쌍'으로 존재해야 하므로)

그리고 순서가 혼동되지 않게
least와 greatest를 조합하면 어렵지 않게 구할 수 있음.

WITH result
     AS (SELECT Least(c1.x, c2.x)    AS "x",
                Greatest(c1.y, c2.y) AS "y",
                Count(*)             AS "cnt"
         FROM   coordinates c1
                JOIN coordinates c2
                  ON c1.y = c2.x
                     AND c1.x = c2.y
         GROUP  BY 1,
                   2
         HAVING cnt >= 2)
SELECT DISTINCT x,
                y
FROM   result
ORDER  BY 1,
          2;

문제 링크

WITH a
     AS (SELECT city,
                Hour(call_time) AS "hour",
                Count(*)        AS "cnt"
         FROM   calls
         GROUP  BY 1,
                   2),
     b
     AS (SELECT city,
                Max(cnt) AS "peak"
         FROM   a
         GROUP  BY 1)
SELECT a.city,
       hour AS "peak_calling_hour",
       peak AS "number_of_calls"
FROM   a
       LEFT JOIN b
              ON a.city = b.city
WHERE  a.cnt = b.peak
ORDER  BY 2 DESC,
          1 DESC;

문제 링크

SELECT Round(Sum(item_count * order_occurrences) / Sum(order_occurrences), 2) AS
       "average_items_per_order"
FROM   orders;

부트캠프 마지막 주말이 지나간다.