Rising Temperature, risingtemperature
Given a Weather table, write a SQL query to find all dates Ids with higher temperature compared to its previous (yesterday's) dates. + --------- + ------------ + ---------------- + | Id (INT) | Date (DATE) | Temperature (INT) | + --------- + ------------ + ---------------- + | 1 | 2015-01-01 | 10 | 2 | 2015-01-02 | 25 | 3 | 2015-01-03 | 20 | 4 | 2015-01-04 | 30 | + --------- + ------------ + --------------- --- + For example, return the following Ids for the above Weather table: + ---- + | Id | + ---- + | 2 | 4 | + ---- + thoughts: You need to know two variables, first, the difference between the two dates before and after, and then the temperature difference between the two dates before and after. 1. select * from weather + ------ + ------------ + ------------- + | id | date | temperature | + ------ + ------------ + ------------- + | 1 | 2000-12-15 | 3 | 2 | 2000-12-16 | 5 | 3 | 2000-12-17 | 1 | 4 | 2000-12-14 | 5 | 5 | 2000-12-18 | 0 | + ------ + ------------ + ------------- + 2. Sort mysql> select * from Weather order date; + ------ + ------------ + ------------- + | id | date | temperature | + ------ + ------------ + ------------- + | 4 | 2000-12-14 | 5 | 1 | 2000-12-15 | 3 | 2 | 2000-12-16 | 5 | 3 | 2000-12-17 | 1 | 5 | 2000-12-18 | 0 | + ------ + ------------ + ------------- + 3. Obtain the select id of Temperature Difference and time difference, datediff (date, @ predate) as datediff, temperature, @ tempdiff: = temperature-@ pretemp as 'tempdiff ', @ pretemp: = temperature as 'pretemp', @ predate: = date as 'predate' from (select * from Weather order by date) t, (select @ predate: = '2017-01-01 ') l, (select @ pretemp: = 0) pret; + ------ + ---------- + response + ---------- + --------- + ------------ + | id | datediff | temperature | tempdiff | pretemp | predate | + ------ + ---------- + --------------- + ---------- + | 4 | 365590 | 5 | 5 | 5 | 2000-12-14 | 1 | 1 | 3 |-2 | 3 | 2000-12-15 | 2 | 1 | 5 | 2 | 5 | 2000-12-16 | 3 | 1 | 1 |-4 | 1 | 2000-12-17 | 5 | 1 | 0 |-1 | 0 | 2000-12-18 | + ------ + ---------- + ------------- + ---------- + --------- + ------------ + 4. Extract id select id from (select * from (select id, datediff (date, @ predate) as diff, temperature, @ tempdiff: = temperature-@ pretemp as 'tempdiff ', @ pretemp: = temperature as 'pretemp', @ predate: = date as 'predate' from (select * from Weather order by date) h) t, (select @ predate: = '2017-01-01 ') l, (select @ pretemp: = 0) pret) w where w. diff = 1 and w. tempdiff> 0; + ------ + | id | + ------ + | 2 | + ------ +