Rising Temperature - Leetcode Solution

The rising temperature problem on LeetCode is a SQL problem that requires you to find all the dates where the temperature was higher than the temperature of the previous day. Below is a detailed solution for the problem:

Problem Statement: Write an SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.

Solution:

To solve this problem, we will need to use a self-join to compare each day's temperature with the temperature of the previous day. We can do this by creating two identical tables, one for the current day's temperature and one for the previous day's temperature, and then joining them on the date.

Here's the SQL query for the problem:

SELECT weather.id FROM weather JOIN weather AS prevWeather ON TO_DAYS(weather.recordDate) - TO_DAYS(prevWeather.recordDate) = 1 WHERE weather.Temperature > prevWeather.Temperature;

Explanation of the Solution:

In this query, we have used the following clauses:

• SELECT: This clause is used to select the Id of the date where the temperature was higher than the temperature of the previous day.
• JOIN: We have used a self-join to join the weather table with itself. We have used the alias "prevWeather" for the previous day's temperature table to avoid ambiguity.
• ON: This clause is used to join the weather table with the previous day's temperature table. We have calculated the difference between the record date of the current day and the record date of the previous day using the TO_DAYS function. If the difference is 1, it means that the date is the previous date.
• WHERE: We have used the WHERE clause to filter the rows where the temperature of the current day is higher than the temperature of the previous day.

Note: The TO_DAYS function is used to convert a date into a number of days since year 0.

Conclusion:

The rising temperature problem on LeetCode can be easily solved using a self-join and by comparing the temperature of the current day with the previous day. The solution is simple, efficient, and can be easily implemented in SQL.

1