240827_TIL

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

SQL 코드카타

문제 링크
난이도는 hard지만 별로 어렵지 않은 문제.

년도별/product_id 별로 데이터가 여러 군데 흩어져 있을 수 있으니,
그냥 spend가 아니라 sum(spend)를 써서
product_id별로 spend의 합부터 구해야 한다 주의.

WITH result
     AS (SELECT Year(transaction_date)               AS "year",
                product_id,
                Sum(spend)                           AS "curr_year_spend",
                Lag(Sum(spend))
                  OVER(
                    partition BY product_id
                    ORDER BY Year(transaction_date)) AS "prev_year_spend"
         FROM   user_transactions
         GROUP  BY 1,
                   2)
SELECT year,
       product_id,
       curr_year_spend,
       prev_year_spend,
       Round(100.0 * ( curr_year_spend - prev_year_spend ) / prev_year_spend, 2)
       AS
       "yoy_rate"
FROM   result
ORDER  BY 2,
          1;

문제 링크
모든 날짜가 다 살아있어야 하므로
transactions 테이블에서 고유 날짜를 가져온 후에
odd와 even을 left join하는 방식으로 해결.

WITH even
     AS (SELECT transaction_date,
                Sum(amount) AS "even_sum"
         FROM   transactions
         WHERE  amount%2 = 0
         GROUP  BY 1),
     odd
     AS (SELECT transaction_date,
                Sum(amount) AS "odd_sum"
         FROM   transactions
         WHERE  amount%2 = 1
         GROUP  BY 1)
SELECT DISTINCT t.transaction_date,
                Ifnull(odd_sum, 0)  AS "odd_sum",
                Ifnull(even_sum, 0) AS "even_sum"
FROM   transactions t
       LEFT JOIN odd o
              ON t.transaction_date = o.transaction_date
       LEFT JOIN even e
              ON t.transaction_date = e.transaction_date
ORDER  BY 1;

문제 링크
CTE b를 만드는 단계에서 rank() over() 함수를 쓸 때,
그냥 transaction_date의 desc가 아니라
(그루핑이 걸려있으므로) max(transaction_date)의 desc로 정렬해야 함 주의.

WITH a
     AS (SELECT customer_id,
                Sum(amount)
                AS
                   "total_amount",
                Count(transaction_id)
                AS
                   "transaction_count",
                Count(DISTINCT category)
                AS
                   "unique_categories",
                Round(Avg(amount), 2)
                AS
                   "avg_transaction_amount",
                Round(( Count(transaction_id) * 10.0 ) + ( Sum(amount) / 100 ),
                2) AS
                "loyalty_score"
         FROM   Transactions t
                LEFT JOIN Products p
                       ON t.product_id = p.product_id
         GROUP  BY 1),
     b
     AS (SELECT t.customer_id,
                category,
                Rank()
                  OVER(
                    partition BY customer_id
                    ORDER BY Count(category) DESC, Max(transaction_date) DESC)
                AS
                   "ranking"
         FROM   Transactions t
                LEFT JOIN Products p
                       ON t.product_id = p.product_id
         GROUP  BY 1,
                   2),
     c
     AS (SELECT customer_id,
                category
         FROM   b
         WHERE  ranking = 1)
SELECT a.customer_id,
       total_amount,
       transaction_count,
       unique_categories,
       avg_transaction_amount,
       c.category AS "top_category",
       loyalty_score
FROM   a
       LEFT JOIN c
              ON a.customer_id = c.customer_id
ORDER  BY 7 DESC,
          1;

문제 링크
이 문제의 킥은 hierarchy_level을 구하는 것.
그것만 성공하면 나머지는 어렵지 않은데,
딱 그걸 생각하는 게 어려웠다.

tree구조로 되어있는 employee들을
CEO 기준으로 몇 단계 떨어져 있는지를 구하는 게 필요한데,
테이블이 몇 row나 있을지 모르고 CEO와의 단계가 몇 개 떨어져 있을지 모르므로
이걸 무작정 join이나 window 함수로 해결할 수는 없다.

따라서, recursive CTE를 써서
먼저 CEO에 해당하는 경우부터 hierarchy_level을 0으로 두고
join 조건에 해당할 때마다 1씩 증가시켜 나가는 과정을 반복하는 것이
가장 정확한 방법이다. 아래는 완성된 정답 쿼리.

WITH recursive result
AS
  (
         SELECT employee_id,
                employee_name,
                0 AS hierarchy_level,
                salary
         FROM   employees
         WHERE  manager_id IS NULL
         UNION ALL
         SELECT e.employee_id,
                e.employee_name,
                hierarchy_level + 1,
                e.salary        -
                (
                       SELECT salary
                       FROM   employees
                       WHERE  manager_id IS NULL)
         FROM   employees e
         JOIN   result r
         ON     r.employee_id = e.manager_id )
  SELECT   employee_id   AS subordinate_id,
           employee_name AS subordinate_name,
           hierarchy_level,
           salary AS salary_difference
  FROM     result
  WHERE    hierarchy_level <> 0
  ORDER BY 3,
           1;

문제 링크
기본적인 window함수 활용 문제.

SELECT team_id,
       team_name,
       3 * wins + 1 * draws + 0 * losses          AS "points",
       Rank()
         OVER(
           ORDER BY 3*wins+1*draws+0*losses DESC) AS "position"
FROM   teamstats
ORDER  BY 3 DESC,
          2;