197. Rising Temperature

Description

Table: Weather

Column Name	Type
id	int
recordDate	date
temperature	int

id is the primary key for this table.

This table contains information about the temperature in a certain day.

Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

Constraints

Approach

Links

• GeeksforGeeks

• Leetcode

• ProgramCreek

• YouTube

Examples

Example 1

Input:

Weather

id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

Output:

Id
2
4

Explanation:

In 2015-01-02, temperature was higher than the previous day (10 -> 25).

In 2015-01-04, temperature was higher than the previous day (30 -> 20).

Solutions

Solution 1

# MySQL query statement

SELECT W1.id AS 'Id'
FROM Weather W1
JOIN Weather W2 ON DATEDIFF(W1.recordDate, W2.recordDate) = 1 
                    AND W1.temperature > W2.temperature;

Follow up