[ 알고리즘 ] LeetCode 2314. The First Day of the Maximum Recorded Degree in Each City

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

본 글은 [ LeetCode ] 2314. The First Day of the Maximum Recorded Degree in Each City를 풀고 작성한 글입니다.

문제

테이블

Table: Weather

+-------------+------+
| Column Name | Type |
+-------------+------+
| city_id     | int  |
| day         | date |
| degree      | int  |
+-------------+------+
(city_id, day) is the primary key for this table.
Each row in this table contains the degree of the weather of a city on a certain day.
All the degrees are recorded in 2022.

요구사항

Write an SQL query to report the day that has the maximum recorded degree. If the maximum degree was recorded for the same city multiple times, return the earliest day among them.

Return the result table ordered by city_id in ascending order.

풀이

접근법

RANK, DENSE_RANK, 또는 ROW_NUMBER 윈도우 함수를 사용하여 degree 및 day 필드를 기준으로 각각 내림차순, 오름차순 정렬해서 순위를 매기고 이를 WHERE 구를 활용해 필요한 값만 조회하면 된다.

나의 풀이

접근법을 토대로 풀면 아래와 같다.

SELECT
    city_id,
    day,
    degree
FROM (
    SELECT
        city_id,
        day,
        degree,
        RANK() OVER(PARTITION BY city_id ORDER BY degree DESC, day ASC) AS weather_rank
    FROM Weather
) AS WeatherRanks
WHERE weather_rank = 1
ORDER BY city_id ASC;