Rising Temperature - Solution & Explanation
Problem Statement
Table: Weather
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id is the column with unique values for this table. There are no different rows with the same recordDate. This table contains information about the temperature on a certain day.
Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Weather table: +----+------------+-------------+ | id | recordDate | temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+ Output: +----+ | id | +----+ | 2 | | 4 | +----+ Explanation: In 2015-01-02, the temperature was higher than the previous day (10 -> 25). In 2015-01-04, the temperature was higher than the previous day (20 -> 30).
Approach Overview
Problem Overview: You are given a Weather table containing id, recordDate, and temperature. The task is to return the IDs where the temperature is higher than the previous day's temperature. Only consecutive calendar days count, so gaps in dates must be handled correctly.
Approach 1: Self-Join Approach (O(n log n) time, O(1) space)
The most common SQL solution compares each row with the previous day using a self join. Join the table with itself where w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY). This pairs every record with the exact previous day's record. After the join, filter rows where w1.temperature > w2.temperature. The database engine handles scanning and join optimization internally, typically resulting in O(n log n) execution due to indexing or sorting on dates. This approach works well in systems where SQL joins are heavily optimized.
Approach 2: Self-Join on Dates (O(n log n) time, O(1) space)
A slight variation explicitly checks the date difference between rows. Instead of shifting the date with DATE_ADD, join two rows and filter using DATEDIFF(w1.recordDate, w2.recordDate) = 1. This guarantees the rows represent consecutive days. Then apply the same temperature comparison. Functionally this is identical to the previous solution but sometimes reads more clearly depending on the SQL dialect. It still relies on relational join logic and is commonly discussed when practicing database interview problems.
Approach 3: Using LAG Window Function (O(n log n) time, O(n) space)
Window functions provide a cleaner solution. Use LAG(temperature) and LAG(recordDate) over an ordered window of recordDate. This gives direct access to the previous row’s temperature and date without joining the table to itself. After computing the lag values, filter rows where the date difference equals one day and the current temperature is greater than the previous temperature. Window functions simplify the logic and are widely supported in modern SQL engines that support window functions.
Approach 4: Using General Window Functions (O(n log n) time, O(n) space)
A broader window-function strategy sorts the rows by date and computes the previous row’s values inside a subquery or CTE. Once you have the previous temperature and date, the final filter condition checks both adjacency and the temperature increase. This approach is especially useful when extending the query to compute trends, rolling averages, or multiple comparisons across rows.
Recommended for interviews: The self-join solution is the expected baseline because it shows you understand relational joins and date comparisons. The LAG window function version is usually considered the cleanest and most modern approach. Showing both demonstrates strong SQL fundamentals and awareness of advanced query features.
Approach 1: Self-Join Approach
This approach uses SQL's self-join capabilities. We join the Weather table with itself by matching the current day's date with the day after the previous date, effectively allowing us to compare temperatures between consecutive days.
This way, we filter for cases where the current day's temperature is higher than the previous day's temperature.
The SQL code joins the Weather table with itself. For each record in the first instance (w1), it compares with records in the second instance (w2) where the difference in days (DATEDIFF) is exactly one, meaning consecutive days. Then, it filters those records where the temperature of w1 is greater than w2.
Code
SQL
Complexity
Time Complexity: O(n), where n is the number of records, as we are checking each record with its previous day's record.
Space Complexity: O(1), as no extra space is used except for the result set.
Approach 2: Using Lag Window Function
This approach utilizes SQL window functions, specifically the LAG function, which allows you to access the value of a column from the previous row.
We use LAG to find the previous day's temperature for each record and then filter the results where the current day's temperature is higher than the previous day's temperature.
The SQL code first creates a subquery with the Weather data, adding a new column prev_temp using the LAG window function. LAG(temperature, 1) gives the temperature of the previous day. Then, the outer query selects rows where the current day's temperature is greater than prev_temp.
Code
SQL
Complexity
Time Complexity: O(n), due to scanning through all rows once with window function.
Space Complexity: O(n), due to the subquery which temporarily holds additional columns.
Approach 3: Self-Join on Dates
This approach involves performing a self-join on the Weather table. We join the table with itself where the recordDate of the second table (alias W2) is one day earlier than the recordDate of the first table (alias W1). This allows us to compare the temperatures of each date with its previous day.
This SQL solution utilizes a self-join to compare the temperature of each day with its previous day. We join the Weather table with itself using the condition that W1's recordDate should be exactly one day after W2's recordDate. Then, we select the IDs where W1's temperature is greater than W2's temperature.
Code
SQL
Complexity
Time Complexity: O(n log n) due to the use of joins, assuming a general sort operation for join implementation. Space Complexity: O(n) for storing join results.
Approach 4: Using Window Functions
This approach leverages window functions to compare each day's temperature with the previous day's temperature without explicitly joining the table. By using the LAG function, we can access the previous record's temperature for comparison.
This solution employs the LAG window function to access the temperature of the previous day for each record. We create a subquery that includes a column for the previous day's temperature calculated by LAG. Finally, we filter where the current temperature is greater than the previous day's temperature.
Code
SQL
Complexity
Time Complexity: O(n) as window functions efficiently traverse the data in one pass. Space Complexity: O(n) to store intermediate results of window functions.
Approach 5: Self-Join + DATEDIFF/SUBDATE Function
We can use self-join to compare each row in the Weather table with its previous row. If the temperature is higher and the date difference is one day, then it is the result we are looking for.
Approach 6: Default Approach
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Self-Join Approach | Time Complexity: O(n), where n is the number of records, as we are checking each record with its previous day's record. |
| Using Lag Window Function | Time Complexity: O(n), due to scanning through all rows once with window function. |
| Self-Join on Dates | Time Complexity: O(n log n) due to the use of joins, assuming a general sort operation for join implementation. Space Complexity: O(n) for storing join results. |
| Using Window Functions | Time Complexity: O(n) as window functions efficiently traverse the data in one pass. Space Complexity: O(n) to store intermediate results of window functions. |
| Self-Join + DATEDIFF/SUBDATE Function | — |
| Default Approach | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join Approach | O(n log n) | O(1) | Classic SQL interview solution using joins between rows |
| Self-Join on Dates | O(n log n) | O(1) | When explicitly comparing date differences using DATEDIFF |
| Using LAG Window Function | O(n log n) | O(n) | Cleaner modern SQL approach with window functions |
| General Window Function Strategy | O(n log n) | O(n) | Useful when extending analysis with additional row comparisons |
Video Solution
Rising Temperature | Leetcode 197 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 31,325 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Rising Temperature easy or hard?
How to solve Rising Temperature in O(n)?
What is the best approach for Rising Temperature?
Rising Temperature Python or Java solution exists?
What data structure is used in Rising Temperature?
What is the time complexity of Rising Temperature?
Is Rising Temperature asked at Google, Amazon, or Meta?
Ready to solve this problem?
Practice Rising Temperature with our built-in code editor and test cases.
Practice on FleetCode