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
  • day is a valid date in the year 2022
  • -100 ≤ degree ≤ 100
  • Each (city_id, day) combination is unique

Visualization

Tap to expand
First Day of Maximum Recorded Degree in Each City INPUT: Weather Table city_id day degree 1 2022-01-07 24 1 2022-03-07 31 1 2022-01-17 27 2 2022-01-10 28 2 2022-01-15 22 2 2022-01-20 28 Note: City 2 has same max (28) on two different days Columns: city_id, day, degree Records daily temperatures for cities in year 2022 ALGORITHM STEPS 1 Find MAX degree per city GROUP BY city_id MAX(degree) 2 Join back to Weather Match city_id and degree to find all max days 3 Select earliest day Use MIN(day) or ROW_NUMBER() + ORDER BY 4 Order by city_id ASC Return city_id, day, degree WITH MaxTemp AS ( SELECT city_id, MAX(degree) as max_deg FROM Weather GROUP BY city_id )... FINAL RESULT city_id day degree 1 2022-03-07 31 2 2022-01-10 28 Explanation: City 1: Max = 31 on Mar 7 City 2: Max = 28 on Jan 10 (earlier than Jan 20) OK Ordered by city_id ASC Earliest day selected Key Insight: Use a subquery or CTE to first identify the maximum temperature for each city, then join back to the original table to find all days with that max temperature. Finally, use MIN(day) or window functions like ROW_NUMBER() with ORDER BY day ASC to select only the earliest occurrence for each city. TutorialsPoint - The First Day of the Maximum Recorded Degree in Each City | Optimal Solution

Related Problems

Asked in
Amazon 12 Google 8 Facebook 6
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen