240817_TIL

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

SQL 코드카타

문제 링크

WITH a
     AS (SELECT flight_id,
                Count(passenger_id) AS "book"
         FROM   passengers
         GROUP  BY 1),
     b
     AS (SELECT f.flight_id,
                CASE
                  WHEN capacity <= book THEN capacity
                  ELSE book
                END AS 'booked_cnt',
                book
         FROM   flights f
                LEFT JOIN a
                       ON f.flight_id = a.flight_id)
SELECT flight_id,
       Ifnull(booked_cnt, 0)        AS "booked_cnt",
       Ifnull(book - booked_cnt, 0) AS "waitlist_cnt"
FROM   b
ORDER  BY 1;

문제 링크
위의 문제를 약간 응용한 문제.
난이도는 hard라고 되어있지만 크게 어렵진 않다.

WITH a
     AS (SELECT passenger_id,
                flight_id,
                booking_time,
                Rank()
                  OVER(
                    partition BY flight_id
                    ORDER BY booking_time) AS "rank"
         FROM   passengers),
     confirmed
     AS (SELECT passenger_id,
                "Confirmed" AS "Status"
         FROM   flights f
                LEFT JOIN a
                       ON f.flight_id = a.flight_id
                          AND f.capacity >= a.rank)
SELECT p.passenger_id,
       Ifnull(status, 'Waitlist') AS "Status"
FROM   passengers p
       LEFT JOIN confirmed c
              ON p.passenger_id = c.passenger_id
ORDER  BY 1;

문제 링크

WITH a
     AS (SELECT voter,
                Ifnull(1 / Count(DISTINCT candidate), 0) AS "cnt"
         FROM   votes
         GROUP  BY 1),
     result
     AS (SELECT v.candidate,
                Sum(cnt) AS "total"
         FROM   votes v
                LEFT JOIN a
                       ON v.voter = a.voter
         GROUP  BY 1)
SELECT candidate
FROM   result
WHERE  total = (SELECT Max(total)
                FROM   result)
ORDER  BY 1;

문제 링크

SELECT u.user_id,
       u.name,
       Ifnull(Sum(r.distance), 0) AS "traveled distance"
FROM   users u
       LEFT JOIN rides r
              ON u.user_id = r.user_id
GROUP  BY 1,
          2
ORDER  BY 1;

문제 링크

WITH result
     AS (SELECT department,
                salary,
                Dense_rank()
                  OVER(
                    partition BY department
                    ORDER BY salary DESC) AS "ranking"
         FROM   salaries
         WHERE  department IN ( 'Engineering', 'Marketing' ))
SELECT Max(salary) - Min(salary) AS "salary_difference"
FROM   result
WHERE  ranking = 1;

주말 코드카타 끝!
이제 최종발표 스크립트 다듬어야지