240822_TIL

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

SQL 코드카타

문제 링크
이 문제의 킥은 prime_eligible의 연산 결과를
not_prime 행에 붙여서 쓸 수 있느냐 여부.
이런 형태로 연산이 가능하려면 window함수를 쓰면 된다.

먼저 item_type별로 square_footage의 합과 count를 구하고
그 뒤에 lead 함수를 써서 not_prime 행에 prime_eligible의 값이 걸리게 한다.
이 결과를 result라는 CTE에 저장한다.

WITH result
     AS (SELECT item_type,
                Sum(square_footage)     AS "sum_space",
                Count(DISTINCT item_id) AS "cnt",
                Lead(Sum(square_footage))
                  OVER(
                    ORDER BY item_type) AS "lead_space",
                Lead(Count(DISTINCT item_id))
                  OVER(
                    ORDER BY item_type) AS "lead_cnt"
         FROM   inventory
         GROUP  BY 1)

이 CTE를 실행시키면 아래와 같은 결과가 나온다.

이제 본 쿼리에서 바로 결과를 구할 수 있다.

WITH result
     AS (SELECT item_type,
                Sum(square_footage)     AS "sum_space",
                Count(DISTINCT item_id) AS "cnt",
                Lead(Sum(square_footage))
                  OVER(
                    ORDER BY item_type) AS "lead_space",
                Lead(Count(DISTINCT item_id))
                  OVER(
                    ORDER BY item_type) AS "lead_cnt"
         FROM   inventory
         GROUP  BY 1)
SELECT item_type,
       Round(CASE
               WHEN item_type = 'prime_eligible' THEN
               Floor(500000 / sum_space) * cnt
               ELSE Floor(( 500000 - Floor(500000 / lead_space) * lead_space ) /
                          sum_space) * cnt
             END, 0) AS "item_count"
FROM   result
ORDER  BY 2 DESC;

item_type이 prime_eligible이면
500,000을 sum_space로 나눈 값에 cnt를 곱한 값을 item_count(5,400)로 쓰고,
아니면 (즉, not_prime일 경우) lead_space나 lead_cnt를 써서 구하면 된다.

한 row의 연산 결과까지는 쉽게 구할 수 있었는데,
이 결과를 다른 row에 붙여서 쓰는 법이 떠오르지 않아 한참 고민했다.

문제 링크

SELECT CASE
         WHEN A >= ( B + C )
               OR B >= ( A + C )
               OR C >= ( A + B ) THEN 'Not A Triangle'
         WHEN A = B
              AND B = C THEN 'Equilateral'
         WHEN A <> B
              AND B <> C
              AND A <> C THEN 'Scalene'
         ELSE 'Isosceles'
       end AS triangle_type
FROM   triangles;

문제 링크

SELECT N,
       ( CASE
           WHEN P IS NULL THEN "Root"
           WHEN N IN (SELECT P
                      FROM   Tree) THEN "Inner"
           ELSE "Leaf"
         end ) AS 'Type'
FROM   Tree
ORDER  BY 1;

문제 링크
오랜만에 써 본 window함수 percent_rank
예전에 풀었던 코드카타에서도 썼던 적이 있다.

partition을 기준으로 0부터 1까지의 percentile을 계산해주는 함수인데,
행이 3개면 0, 0.5, 1
행이 4개면 0, 0.25, 0.75, 1
이렇게 알아서 0부터 1까지를 기준으로 percentile을 끊어준다.

이 문제에서는 5%, 즉 0.05가 기준이기 때문에
CTE에서 pct라는 이름의 컬럼을 먼저 구해놓고
본 쿼리의 where 절에서 pct <= .05 로 써 주면 된다.

WITH result
     AS (SELECT policy_id,
                state,
                fraud_score,
                Percent_rank()
                  OVER(
                    partition BY state
                    ORDER BY fraud_score DESC) AS "pct"
         FROM   fraud)
SELECT policy_id,
       state,
       fraud_score
FROM   result
WHERE  pct <= .05
ORDER  BY 2,
          3 DESC,
          1;

문제 링크
open과 send 중 0이 있을 수 있기 때문에
본 쿼리에서 ifnull을 여러 개 써야 하는 게 조금 귀찮은 부분.

WITH send
     AS (SELECT ag.age_bucket,
                SUM(time_spent) AS "sum_send"
         FROM   activities act
                inner join age ag
                        ON act.user_id = ag.user_id
         WHERE  activity_type = 'send'
         GROUP  BY 1),
     open
     AS (SELECT ag.age_bucket,
                SUM(time_spent) AS "sum_open"
         FROM   activities act
                inner join age ag
                        ON act.user_id = ag.user_id
         WHERE  activity_type = 'open'
         GROUP  BY 1)
SELECT s.age_bucket,
       Round(100.0 * Ifnull(sum_send, 0) / (
             Ifnull(sum_send, 0) + Ifnull(sum_open, 0)
                                           ), 2) AS "send_perc",
       Round(100.0 * Ifnull(sum_open, 0) / (
             Ifnull(sum_send, 0) + Ifnull(sum_open, 0)
                                           ), 2) AS "open_perc"
FROM   send s
       left join open o
              ON s.age_bucket = o.age_bucket;

풀어지지 말고 매일 해야 할 일을 조금씩 하자.
수료를 했다 뿐이지 아직 아무것도 얻은 건 없다.