Leetcode SQL 50 -- 197. Rising Temperature

LeetCode > SQL_50 SQL

Created At : 2024-11-05 18:42

Count:186 Views :

Question
1. Table name: Weather
Explaination
1. INTERVAL <value> <unit>
2. DATEADD(<interval>, <number>, <date>)
Solution

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;