197 Rising Temperature

Description

Table: Weather

• id is the primary key for this table.
• This table contains information about the temperature on a certain day.

Problem Statement:

Write a SQL query to find all dates’ Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Weather table:

Output:

Explanation:

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

Solution

This problem can be solved in two ways.

Using LEAD or LAG function

If you had a way to find previous or next day temperature then you can find all dates where the temperature was higher than the previous day. In order to find temperature for next day, you can use LEAD function and to find temperature for previous day, you can use LAG function. You need to use one of these two, but not both. In order to arrange values in the correct order, you need to order the window by recordDate in ascending order.

1WITH temperatures AS (
2    SELECT id, recordDate, temperature,
3        LAG(temperature, 1) OVER (ORDER BY recordDate) AS previous_day_temperature
4        FROM Weather
5) SELECT id FROM temperatures
6    WHERE temperature > previous_day_temperature;

This may look like correct solution but what if two recordDates are not consecutive. In those cases, it will incorrectly return the results. For example, if input is like below.

The above query will return the result as below.

This is incorrect because the dates 2015-01-01 and 2015-01-03 are not consecutive. In order to avoid this type of errors, you also need to ensure that the recordDate is consecutive. Again, you can use LAG function but on recordDate to verify this. The final query will be like this.

1WITH temperatures AS (
2    SELECT id, recordDate, temperature,
3        LAG(temperature, 1) OVER (ORDER BY recordDate) AS previous_day_temperature,
4        LAG(recordDate, 1) OVER (ORDER BY recordDate) AS previous_day
5        FROM Weather
6) SELECT id FROM temperatures
7    WHERE temperature > previous_day_temperature AND DATEDIFF(recordDate, previous_day) = 1;

Using SELF JOIN

You could join the Weather table with itself on the condition that recordDate of left table is one day before recordDate of right table. You could also include the condition that the temperature of left table is greater than the temperature of right table.

1SELECT w2.id 
2    FROM Weather w1, Weather w2
3    WHERE DATEDIFF(w2.recordDate, w1.recordDate) = 1 AND w2.temperature > w1.temperature;

This can also be written as a JOIN query like below.

1SELECT w2.id 
2    FROM Weather w1
3    JOIN Weather w2
4    ON DATEDIFF(w2.recordDate, w1.recordDate) = 1 AND w2.temperature > w1.temperature;