Rising Temperature - Video Solutions

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.