240729_TIL

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

SQL 코드카타

프로젝트 기간 주중에는 한 문제만 해결하자.
늘 그렇듯 놓지만 않으면 된다.

문제 링크

WITH a
     AS (SELECT departure_airport AS "airport_id",
                flights_count
         FROM   flights
         UNION ALL
         SELECT arrival_airport,
                flights_count
         FROM   flights),
     b
     AS (SELECT airport_id,
                Sum(flights_count) AS "traffic"
         FROM   a
         GROUP  BY 1),
     result
     AS (SELECT airport_id,
                Dense_rank()
                  OVER(
                    ORDER BY traffic DESC) AS "ranking"
         FROM   b)
SELECT airport_id
FROM   result
WHERE  ranking = '1';