240728_TIL

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

SQL 코드카타

문제 링크

WITH a
     AS (SELECT candidate_id,
                interview_id
         FROM   candidates
         WHERE  years_of_exp >= 2),
     b
     AS (SELECT a.candidate_id,
                a.interview_id,
                Sum(r.score) AS "sum_score"
         FROM   a
                LEFT JOIN rounds r
                       ON a.interview_id = r.interview_id
         GROUP  BY 1,
                   2)
SELECT candidate_id
FROM   b
WHERE  sum_score > 15; 

문제 링크
생각하는 게 복잡했던 문제.

caller와 recipient를 구분하지 않기 때문에 CTE a에서 둘을 하나로 합치고,
CTE b에서 첫 통화와 마지막 통화를 구분하기 위해 rank를 써서
각각 오름차순과 내림차순으로 정의해 준다.

마지막으로 b를 b와 자체 조인하면서
user id가 같고 first도 1, last도 1인 조건을 찾아
고유 id를 출력하면 정답.

WITH a
     AS (SELECT caller_id    AS "user1",
                recipient_id AS "user2",
                call_time
         FROM   calls
         UNION ALL
         SELECT recipient_id AS "user1",
                caller_id    AS "user2",
                call_time
         FROM   calls),
     b
     AS (SELECT user1,
                user2,
                Rank()
                  OVER(
                    partition BY user1, Date(call_time)
                    ORDER BY call_time)      AS "first",
                Rank()
                  OVER(
                    partition BY user1, Date(call_time)
                    ORDER BY call_time DESC) AS "last"
         FROM   a)
SELECT DISTINCT b1.user1 AS "user_id"
FROM   b b1
       JOIN b b2
         ON b1.user1 = b2.user1
            AND b1.user2 = b2.user2
            AND b1.first = 1
            AND b2.last = 1;

문제 링크

WITH NY
     AS (SELECT count(student_id) AS "cnt_n"
         FROM   NewYork
         WHERE  score >= 90),
     CF
     AS (SELECT count(student_id) AS "cnt_c"
         FROM   California
         WHERE  score >= 90)
SELECT CASE
         WHEN cnt_n > cnt_c THEN "New York University"
         WHEN cnt_n < cnt_c THEN "California University"
         WHEN cnt_n = cnt_c THEN "No Winner"
       END AS "winner"
FROM   NY,
       CF;

문제 링크
난이도는 medium이지만 간단하게 풀리는 문제.

WITH result
     AS (SELECT account_id,
                day,
                CASE
                  WHEN type = 'Deposit' THEN amount
                  WHEN type = 'Withdraw' THEN -amount
                END AS "real_amount"
         FROM   transactions)
SELECT account_id,
       day,
       Sum(real_amount)
         OVER (
           partition BY account_id
           ORDER BY day) AS "balance"
FROM   result;

문제 링크
CTE에서 승객별 탑승시간을 먼저 구한다.
(5분에 도착하든, 6분에 도착하든 버스는 7분대에 도착하는 걸 타야 하므로.)

그리고 본 쿼리에서는 buses 테이블과 result CTE를 조인해서
bus_id별로 숫자를 세 주면 정답.

WITH result
     AS (SELECT passenger_id,
                Min(b.arrival_time) AS arrival_time
         FROM   passengers p
                INNER JOIN buses b
                        ON p.arrival_time <= b.arrival_time
         GROUP  BY passenger_id)
SELECT bus_id,
       Count(r.arrival_time) AS passengers_cnt
FROM   buses b
       LEFT JOIN result r
              ON b.arrival_time = r.arrival_time
GROUP  BY 1
ORDER  BY 1;