SQL implementation of LeetCode (197. Rising temperature)

[LeetCode] 197.Rising Temperature

Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
| 2 |
| 4 |
+----+

This question gives us a Weather table and asks us to find the ID with a higher temperature than the previous day. Since the IDs are not necessarily arranged in order, we have to find the previous day based on the date. We can use the MySQL function Datadiff to calculate the difference between two dates. Our restriction is that the temperature is high and the date difference is 1. See the code below:

Solution 1:

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND DATEDIFF(w1.Date, w2.Date) = 1;

The following solution uses the MySQL TO_DAYS function to convert the date into days, and the rest is the same as above:

Solution 2:

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1;

We can also use the Subdate function to minus 1 from the date, as shown in the code below:

Solution 3:

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND SUBDATE(w1.Date, 1) = w2.Date;

Finally, a completely different solution is used. Two variables, pre_t and pre_d, are used to represent the previous temperature and the previous date respectively. The current temperature must be greater than the previous temperature, and the date difference is 1. If the above two conditions are met, it is selected as Id, otherwise it is NULL. Then pre_t and pre_d are updated to the current values, and the selected Id is not empty:

Solution 4:

SELECT Id FROM (
SELECT CASE WHEN Temperature > @pre_t AND DATEDIFF(Date, @pre_d) = 1 THEN Id ELSE NULL END AS Id,
@pre_t := Temperature, @pre_d := Date 
FROM Weather, (SELECT @pre_t := NULL, @pre_d := NULL) AS init ORDER BY Date ASC
) id WHERE Id IS NOT NULL;

References:

https://leetcode.com/discuss/33641/two-solutions

https://leetcode.com/discuss/52370/my-simple-solution-using-inner-join

https://leetcode.com/discuss/86435/a-simple-straightforward-solution-and-its-very-fast

This is the end of this article about SQL implementation of LeetCode (197. Rising temperature). For more relevant SQL implementation of rising temperature content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!