The First Day of the Maximum Recorded Degree in Each City

The First Day of the Maximum Recorded Degree in Each City - Problem

Database Medium

Weather Station Data Analysis Challenge

You're working as a data analyst for a national weather service that tracks temperature records across multiple cities. Given a database table containing daily temperature readings from various weather stations in 2022, you need to find the earliest date when each city recorded its highest temperature of the year.

The Challenge:
• Each city may have multiple days with the same maximum temperature
• When ties occur, you must return the earliest date
• Results should be ordered by city_id in ascending order

Table Schema:
Weather (city_id INT, day DATE, degree INT)

This problem tests your ability to work with window functions, aggregation, and date handling in SQL - essential skills for data analysis roles at major tech companies.

Input & Output

example_1.sql — Basic Case

$ Input: Weather table: | city_id | day | degree | |---------|------------|--------| | 1 | 2022-01-07 | 10 | | 1 | 2022-01-09 | 20 | | 1 | 2022-01-31 | 15 | | 2 | 2022-01-07 | 30 | | 2 | 2022-01-17 | 35 |

› Output: | city_id | day | |---------|------------| | 1 | 2022-01-09 | | 2 | 2022-01-17 |

💡 Note: For city 1: maximum degree is 20 on 2022-01-09. For city 2: maximum degree is 35 on 2022-01-17. Both cities have only one occurrence of their maximum temperature, so we return those dates.

example_2.sql — Tie Breaking

$ Input: Weather table: | city_id | day | degree | |---------|------------|--------| | 1 | 2022-01-01 | 25 | | 1 | 2022-01-04 | 25 | | 1 | 2022-01-02 | 15 | | 2 | 2022-02-01 | 30 | | 2 | 2022-01-15 | 30 |

› Output: | city_id | day | |---------|------------| | 1 | 2022-01-01 | | 2 | 2022-01-15 |

💡 Note: City 1 has maximum degree 25 on both 2022-01-01 and 2022-01-04, so we return the earlier date (2022-01-01). City 2 has maximum degree 30 on both dates, so we return the earlier date (2022-01-15).

example_3.sql — Single Records

$ Input: Weather table: | city_id | day | degree | |---------|------------|--------| | 1 | 2022-12-26 | -5 | | 2 | 2022-12-27 | 0 | | 3 | 2022-12-28 | 5 |

› Output: | city_id | day | |---------|------------| | 1 | 2022-12-26 | | 2 | 2022-12-27 | | 3 | 2022-12-28 |

💡 Note: Each city has only one temperature record, so each city's single record represents both the maximum temperature and the earliest (and only) date when it occurred.

Constraints

1 ≤ Number of rows in Weather table ≤ 10⁵
1 ≤ city_id ≤ 100
All dates are in year 2022 format (YYYY-MM-DD)
-100 ≤ degree ≤ 100 (temperatures in Celsius)
Each (city_id, day) combination is unique
At least one weather record exists for each city_id in the result

Visualization

Tap to expand

Understanding the Visualization

Group by City

Separate all temperature records by city, like organizing contestants by their home city

Rank Within City

For each city, rank all temperature records by highest degree first, then earliest date for ties

Crown the Winners

Select the #1 ranked record from each city - this gives us the earliest date with maximum temperature

Key Takeaway

🎯 Key Insight: Window functions like ROW_NUMBER() allow us to partition data by groups (cities) and rank within each partition efficiently, solving complex analytical problems in a single SQL query!

Asked in

G Google 45 a Amazon 38 f Meta 32 ⊞ Microsoft 28

The optimal approach uses SQL window functions with ROW_NUMBER() to rank temperature records within each city partition. By ordering by degree DESC, day ASC, we can identify the earliest date with maximum temperature in a single efficient query. This O(n log n) solution significantly outperforms nested subqueries and is the preferred method for production database queries.

Common Approaches

Approach	Time	Space	Notes
✓ Two-Pass CTE Approach	O(n)	O(n)	First pass finds max degrees, second pass finds earliest dates
Window Function (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to rank by temperature and date simultaneously
Nested Subqueries (Brute Force)	O(n²)	O(1)	Use correlated subqueries to find max degree and earliest date separately

Two-Pass CTE Approach — Algorithm Steps

Create CTE to find maximum degree for each city using GROUP BY
Join original table with CTE to find all dates with max temperature
Use MIN function to get the earliest date among max temperature days
Order final results by city_id

Visualization

Tap to expand

Step-by-Step Walkthrough

First CTE

Group by city_id and find MAX(degree) for each city

Second CTE

Join with original table and find MIN(day) for max degrees

Final Select

Return results ordered by city_id

Code -

solution.c — C

// SQL solution using CTE approach
const char* sqlQuery = 
"WITH MaxTemperatures AS ("
"    SELECT city_id, MAX(degree) as max_degree "
"    FROM Weather "
"    GROUP BY city_id"
"), "
"EarliestMaxDays AS ("
"    SELECT w.city_id, MIN(w.day) as earliest_day "
"    FROM Weather w "
"    INNER JOIN MaxTemperatures mt "
"        ON w.city_id = mt.city_id "
"        AND w.degree = mt.max_degree "
"    GROUP BY w.city_id"
") "
"SELECT city_id, earliest_day as day "
"FROM EarliestMaxDays "
"ORDER BY city_id;";

// Example execution would use database library like SQLite C API
// sqlite3* db;
// sqlite3_prepare_v2(db, sqlQuery, -1, &stmt, NULL);
printf("Two-pass CTE approach for finding earliest max temperature days\n");

Time & Space Complexity

Time Complexity

⏱️

O(n)

Two linear passes through the data - one for aggregation, one for joining

✓ Linear Growth

Space Complexity

O(n)

CTE stores intermediate results (one row per city)

⚡ Linearithmic Space

42.3K Views

High Frequency

~18 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Two-Pass CTE Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler