Welcome to Subscribe On Youtube
Question
Formatted question description: https://leetcode.ca/all/197.html
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 |
+----+
Algorithm
The function Datediff is used to calculate the difference between two dates. Our limitation is that the temperature is high and the date difference is 1.
Or, TO_DAYS https://www.w3schools.com/sql/func_mysql_to_days.asp
Code
SQL
SELECT
Weather.id AS 'Id'
FROM Weather
JOIN Weather w
ON DATEDIFF(Weather.RecordDate, w.RecordDate) = 1
AND Weather.Temperature > w.Temperature
;
--
SElECT w1.id
FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature
AND TO_DAYS(w1.RecordDate) - TO_DAYS(w2.RecordDate) = 1