LeetCode: 197. Rising Temperature | Step-by-Step Analysis

While solving this problem, it took me time to understand the solution. Yes. At first, I was unable to solve it, or, you can say, I didn’t have the patience to solve it by myself, which is not a good sign. But, after seeing the solution, it also took me time to understand. That’s why I decided to make a note of how it works as a step-by-step process. so that I can remember and apply the same method to solve it and other questions in the future.

Check out the link below to read the question.

Rising Temperature - LeetCode

Solution:

Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Here is only one table, and according to the question, we have to compare itself to find all dates'Id with higher temperatures compared to their previous dates (yesterday).

Step 1: In this case, we normally join selft join and compare table A1(Weather) data with table A2(Weather).

SELECT w1.*, w2.*
FROM Weather w1
JOIN Weather w2 
order by w1.recordDate;

Output:

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

Step 2: Now, according to question, we want two types of data: table A1 date and one day before in table A2

Example:

Table A1: 1 | 2015–01–01 | 10

Compare with one day before in Table A2: not in the table.

Table A1: 2 | 2015–01–02 | 25

Compare with one day before in Table A2: 1 | 2015–01–01 | 10

To do that we apply a where clause:

WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 ;

This condition only shows us one day before table A1, not after or more than one.

Image to understand the effect of the condition

Full SQL:

--- Full SQL
SELECT w1.*, w2.*
FROM Weather w1
JOIN Weather w2 
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 ;

Output:

          W1 (Table A1)                      W2 (Table A2)
| id | recordDate | temperature | id | recordDate | temperature |
| -- | ---------- | ----------- | -- | ---------- | ----------- |
| 2 | 2015-01-02 | 25           | 1 | 2015-01-01  | 10 |
| 3 | 2015-01-03 | 20           | 2 | 2015-01-02  | 25 |
| 4 | 2015-01-04 | 30           | 3 | 2015-01-03  | 20 |

Step 3: According to question, it ask for higher temperatures compared to its previous dates (yesterday).

The equation will be like this: w1 (temperature) > w2(temperature)

       W1 (Table A1)                      W2 (Table A2)
| id | recordDate | temperature | id | recordDate | temperature |
| -- | ---------- | ----------- | -- | ---------- | ----------- |
| 2 | 2015-01-02 | 25     >    | 1 | 2015-01-01  | 10 | = Yes
| 3 | 2015-01-03 | 20     >    | 2 | 2015-01-02  | 25 | = No
| 4 | 2015-01-04 | 30     >    | 3 | 2015-01-03  | 20 | = Yes

So, we will add one more condition:

w2.temperature < w1.temperature;

Also, we just have to show only the ID

So, finally, the full SQL:

SELECT w1.id
FROM Weather w1
JOIN Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 
AND w2.temperature < w1.temperature;

Output:

| Id |
| -- |
| 2  |
| 4  |

Thanks.