Leetcode SQL 50 -- 197. Rising Temperature

Question

Table name: Weather

Column Name	Type
id	int
recordDate	date
temperature	int

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.

Explaination

Here, we need to learn how to handle the datatype date.
To modify the date, we can use INTERVAL keyword, or DATEADD() function.

INTERVAL <value> <unit>

Interval is a special datatype, that can interact with time and date.

Interval unit includes
microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium

For example, '2001-09-28' + INTERVAL 1 DAY → 2002-09-28

DATEADD(<interval>, <number>, <date>)

Similarly, we can use DATEADD() to modify data with datatype date.

DATEADD(year, 1, '2001-09-28') → 2002-09-28

Solution

SELECT w1.id FROM Weather w1 JOIN Weather w2
ON w1.recordDate = w2.recordDate + INTERVAL 1 DAY
WHERE w1.temperature > w2.temperature;  

---

More SQL 50 questions: here