Problem

Table: Weather

+-------------+------+
| Column Name | Type |
+-------------+------+
| city_id     | int  |
| day         | date |
| degree      | int  |
+-------------+------+
(city_id, day) is the primary key (combination of columns with unique values) 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 the year 2022.

Write a solution to report the day that has the maximum recorded degree in each city. 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.

The result format is shown in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Input: 
Weather table:
+---------+------------+--------+
| city_id | day        | degree |
+---------+------------+--------+
| 1       | 2022-01-07 | -12    |
| 1       | 2022-03-07 | 5      |
| 1       | 2022-07-07 | 24     |
| 2       | 2022-08-07 | 37     |
| 2       | 2022-08-17 | 37     |
| 3       | 2022-02-07 | -7     |
| 3       | 2022-12-07 | -6     |
+---------+------------+--------+
Output: 
+---------+------------+--------+
| city_id | day        | degree |
+---------+------------+--------+
| 1       | 2022-07-07 | 24     |
| 2       | 2022-08-07 | 37     |
| 3       | 2022-12-07 | -6     |
+---------+------------+--------+
Explanation: 
For city 1, the maximum degree was recorded on 2022-07-07 with 24 degrees.
For city 2, the maximum degree was recorded on 2022-08-07 and 2022-08-17 with 37 degrees. We choose the earlier date (2022-08-07).
For city 3, the maximum degree was recorded on 2022-12-07 with -6 degrees.

Solution

Method 1 - Window Function (ROW_NUMBER)

We use a window function to rank each row by degree (descending) and day (ascending) for each city, then select the first row for each city.

Code

1
2
3
4
5
6
7

SELECT city_id, day, degree
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY degree DESC, day ASC) AS rn
  FROM Weather
) t
WHERE rn = 1
ORDER BY city_id ASC;
1
2
3
4
5
6
7

SELECT city_id, day, degree
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY degree DESC, day ASC) AS rn
  FROM Weather
) t
WHERE rn = 1
ORDER BY city_id ASC;
1
2
3
4
5
6
7

SELECT city_id, day, degree
FROM (
  SELECT w.*, ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY degree DESC, day ASC) rn
  FROM Weather w
)
WHERE rn = 1
ORDER BY city_id ASC;

Explanation

  • For each city, we rank rows by degree (descending) and day (ascending).
  • We select the first row for each city (the earliest day with the max degree).
  • The result is ordered by city_id ascending.

Complexity

  • ⏰ Time complexity: O(N log N) where N is the number of rows in Weather.
  • 🧺 Space complexity: O(N) for the window function.