The First Day of the Maximum Recorded Degree in Each City - Problem
You are given a Weather table that records the temperature degree for different cities on various days in 2022.
Write a SQL query to find the first day when the maximum temperature was recorded in each city. If a city has the same maximum temperature on multiple days, return the earliest day.
Return the result table ordered by city_id in ascending order.
Table Schema
Weather
| Column Name | Type | Description |
|---|---|---|
city_id
PK
|
int | Unique identifier for each city |
day
PK
|
date | Date when temperature was recorded |
degree
|
int | Temperature degree recorded on that day |
Primary Key: city_id, day
Note: All degrees are recorded in the year 2022. Primary key is composite of city_id and day.
Input & Output
Example 1 — Multiple Days with Same Max Temperature
Input Table:
| city_id | day | degree |
|---|---|---|
| 1 | 2022-01-07 | 25 |
| 1 | 2022-01-09 | 25 |
| 1 | 2022-01-02 | 20 |
| 2 | 2022-01-03 | 15 |
| 2 | 2022-01-04 | 18 |
| 2 | 2022-01-05 | 20 |
Output:
| city_id | day | degree |
|---|---|---|
| 1 | 2022-01-07 | 25 |
| 2 | 2022-01-05 | 20 |
💡 Note:
For city 1, the maximum temperature is 25 degrees, recorded on both 2022-01-07 and 2022-01-09. We return the earliest date (2022-01-07). For city 2, the maximum temperature is 20 degrees, recorded only on 2022-01-05.
Example 2 — Single Day with Max Temperature
Input Table:
| city_id | day | degree |
|---|---|---|
| 1 | 2022-01-01 | 10 |
| 1 | 2022-01-02 | 15 |
| 1 | 2022-01-03 | 12 |
Output:
| city_id | day | degree |
|---|---|---|
| 1 | 2022-01-02 | 15 |
💡 Note:
City 1 has a clear maximum temperature of 15 degrees on 2022-01-02, so we return that single record.
Constraints
-
1 ≤ city_id ≤ 1000 -
dayis a valid date in the year 2022 -
-100 ≤ degree ≤ 100 -
Each
(city_id, day)combination is unique
Visualization
Tap to expand
Understanding the Visualization
1
Input
Weather data with city, date, and temperature
2
Operation
Rank by temperature and date within each city
3
Output
First occurrence of max temperature per city
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() with proper ordering to handle ties by selecting the earliest occurrence
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code