Rising Temperature

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

Table: Weather

+----+------------+-------------+

| id | recordDate | Temperature |

+----+------------+-------------+

| 1  | 2020-12-01 | 10          |

| 2  | 2020-12-02 | 25          |

| 3  | 2021-01-03 | 20          |

| 4  | 2021-01-04 | 30          |

+----+------------+-------------+

Result table:

+----+

| id |

+----+

| 2  |

| 4  |

+----+

Approach

Mysql

SELECT W2.Id

FROM Weather W1, Weather W2

WHERE W1.Temperature < W2.Temperature

AND TO_DAYS(W1.RecordDate) = TO_DAYS(W2.RecordDate) - 1

Oracle

SELECT W2.Id

FROM Weather W1, Weather W2

WHERE W1.Temperature < W2.Temperature

AND trunc(W1.RecordDate) = trunc(W2.RecordDate) - 1