240717_TIL

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

SQL 코드카타
Premium SQL 50 배지 획득한 날🏅

문제 링크
dense_rank를 써서 푸는 간단한 문제.

WITH result
     AS (SELECT p.product_name,
                o.product_id,
                o.order_id,
                order_date,
                Dense_rank()
                  OVER(
                    partition BY product_id
                    ORDER BY order_date DESC) AS "ranking"
         FROM   orders o
                INNER JOIN products p
                        ON o.product_id = p.product_id
         ORDER  BY 1,
                   2,
                   3)
SELECT product_name,
       product_id,
       order_id,
       order_date
FROM   result
WHERE  ranking = '1';

문제 링크
loginfo 테이블 2개를 cross join해서 모든 경우의 수를 다 조합한 후에

1. account_id끼리는 같고
2. ip_address는 서로 다르고
3. (밴의 조건) 한 account_id, ip_address의 로그인 시점(l1.login)이 같은 account_id, 다른 ip_address를 쓰는 경우의 로그인 기간(between) 내에 들어오는 경우

를 출력하면 된다. 너무 어렵게 생각해서 시간이 많이 걸렸네..

SELECT DISTINCT l1.account_id
FROM   loginfo l1,
       loginfo l2
WHERE  l1.login BETWEEN l2.login AND l2.logout
       AND l1.account_id = l2.account_id
       AND l1.ip_address <> l2.ip_address; 

문제 링크
역시 dense_rank를 쓰면 간단하게 풀리는 문제.

WITH result
     AS (SELECT transaction_id,
                day,
                amount,
                Dense_rank()
                  OVER(
                    partition BY day
                    ORDER BY amount DESC) AS "ranking"
         FROM   transactions)
SELECT transaction_id
FROM   result
WHERE  ranking = '1'
ORDER  BY 1;

문제 링크

1. 고객별로 상품별 주문횟수를 구하고
2. 그 중에서 가장 많이 주문한 상품을 dense_rank를 써서 뽑고 (공동 1등을 인정해야 하므로 dense_rank를 써야 함)
3. 랭킹 1위인 상품들만 뽑고 Product 테이블과 조인해서 product_name까지 가져오면 끝.

WITH a
     AS (SELECT customer_id,
                product_id,
                Count(*) AS cnt
         FROM   orders
         GROUP  BY 1,
                   2
         ORDER  BY 1),
     result
     AS (SELECT customer_id,
                product_id,
                cnt,
                Dense_rank()
                  OVER(
                    partition BY customer_id
                    ORDER BY cnt DESC) AS "ranking"
         FROM   a)
SELECT r.customer_id,
       r.product_id,
       p.product_name
FROM   result r
       INNER JOIN products p
               ON r.product_id = p.product_id
WHERE  ranking = '1';

문제 링크
재귀CTE를 써야 하는 난이도 hard 문제.
task_id와 subtasks_count를 먼저 써 준 다음
이 값을 1씩 줄여가면서 union all 하는 연산을 반복한다.
단, cnt > 1 이상의 조건을 추가해야 cnt-1 > 0이 되면서
subtasks_count가 1까지만 내려갈 수 있다.

재귀CTE만 잘 완성되면 그 다음부터는 간단하다.
Executed 테이블을 left join해서 task_id가 null인 경우로 조건을 걸면
아직 완료되지 않은 task_id와 subtask_id를 구할 수 있음.

WITH recursive total_task
AS
  (
         SELECT task_id,
                subtasks_count AS "cnt"
         FROM   tasks
         UNION ALL
         SELECT task_id,
                cnt-1
         FROM   total_task
         WHERE  cnt > 1)
  SELECT    t.task_id,
            t.cnt AS "subtask_id"
  FROM      total_task t
  LEFT JOIN executed e
  ON        t.task_id = e.task_id
  AND       t.cnt = e.subtask_id
  WHERE     e.task_id IS NULL;

목표했던 대로 Premium SQL 50 풀이 완료!
내일부터는 다시 나머지 문제 풀이로 돌아가야지.

아직도 남은 문제가 100개가 넘어서
캠프 끝날 때까지 문제 마를 걱정은 안 해도 될 듯.