The First Day of the Maximum Recorded Degree in Each City solution and explanation
The 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.
Example 1:
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.
Approach overview
Problem Overview: Each city has multiple temperature records across different days. For every city, return the first day when the maximum recorded degree occurred. If the maximum temperature appears multiple times, the earliest date should be returned.
Approach 1: Aggregation + Join (O(n) time, O(c) space)
Start by computing the maximum recorded degree for each city using GROUP BY city_id with MAX(degree). This produces a small result set containing one row per city with its peak temperature. Next, join this result back to the original table on both city_id and the matching maximum degree. Multiple rows may still exist if the same maximum degree appears on different days. To enforce the “first day” requirement, apply MIN(day) grouped by city_id. This approach relies on standard SQL operations—aggregation, join, and filtering—making it portable across most relational databases. The table scan for aggregation is O(n), and the grouped result size is proportional to the number of cities c, giving O(c) auxiliary space.
Approach 2: Window Function Filtering (O(n) time, O(n) space)
Another clean solution uses SQL window functions. Compute the maximum temperature per city with MAX(degree) OVER (PARTITION BY city_id). This annotates every row with the city’s maximum value without collapsing rows. Filter rows where degree = max_degree, leaving only records that match the peak temperature. Finally, select the earliest day using MIN(day) grouped by city_id. Window functions avoid a separate aggregation join and are often easier to read when supported by the database engine. The scan still processes each record once, giving O(n) time complexity, while storing window computation results leads to roughly O(n) space.
Both solutions rely on core SQL concepts such as GROUP BY, SQL joins, and window functions. The key insight is separating two concerns: identifying the maximum temperature per city, and selecting the earliest occurrence of that maximum.
Recommended for interviews: The aggregation + join approach is the most commonly expected answer. It shows that you understand grouping, joining derived tables, and resolving ties using MIN. The window function version is equally efficient but depends on familiarity with analytic functions, which some interviewers treat as an advanced SQL technique.
Solution
# Write your MySQL query statement below
WITH
T AS (
SELECT
*,
RANK() OVER (
PARTITION BY city_id
ORDER BY degree DESC, day
) AS rk
FROM Weather
)
SELECT city_id, day, degree
FROM T
WHERE rk = 1
ORDER BY 1;Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + Join | O(n) | O(c) | Most common SQL interview pattern when computing group maxima then filtering matching rows |
| Window Function + Filter | O(n) | O(n) | When the database supports analytic functions and you want a concise query without a self join |
Frequently asked questions
Is The First Day of the Maximum Recorded Degree in Each City easy or hard?
The problem is rated Medium because it combines multiple SQL concepts: computing a maximum per group and resolving ties by selecting the earliest date. Developers comfortable with GROUP BY and joins usually solve it quickly.
The First Day of the Maximum Recorded Degree in Each City Python/Java solution
This problem is categorized under Database on LeetCode, so the expected solution is written in SQL rather than Python or Java. The typical answer uses MySQL with GROUP BY and MAX aggregation or a window function approach.
How to solve The First Day of the Maximum Recorded Degree in Each City in O(n)?
First compute MAX(degree) for each city using GROUP BY. Join the result with the original table on city_id and the maximum degree to keep only rows that match the peak temperature. Then group by city_id and select MIN(day) to return the earliest date when that maximum occurred.
What is the best approach for The First Day of the Maximum Recorded Degree in Each City?
The most common approach uses GROUP BY to compute the maximum degree per city and then joins that result back to the table to find rows where the degree equals the maximum. Applying MIN(day) ensures the earliest occurrence is selected. This solution runs in O(n) time because the table is scanned once for aggregation and once for the join.
Is The First Day of the Maximum Recorded Degree in Each City asked at Google/Amazon/Meta?
SQL aggregation and grouping problems like this frequently appear in data-focused interviews at companies such as Amazon, Meta, and Google. The pattern of computing a group maximum and selecting the earliest or latest occurrence is especially common in analytics engineering and data analyst interviews.
What data structure is used in The First Day of the Maximum Recorded Degree in Each City?
The solution relies on relational database operations rather than traditional data structures. Key SQL concepts include GROUP BY aggregation, joins with derived tables, and optionally window functions like MAX() OVER(PARTITION BY city_id).
What is the time complexity of The First Day of the Maximum Recorded Degree in Each City?
The optimal SQL solution runs in O(n) time where n is the number of weather records. The database performs a table scan to compute MAX(degree) per city and then filters or joins matching rows. Space complexity is typically O(c) where c is the number of distinct cities.