# Simple Solution

• ``````SELECT wt1.Id
FROM Weather wt1, Weather wt2
WHERE wt1.Temperature > wt2.Temperature AND
TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;
``````

EXPLANATION:

TO_DAYS(wt1.DATE) return the number of days between from year 0 to date DATE
TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1 check if wt2.DATE is yesterday respect to wt1.DATE

We select from the joined tables the rows that have

wt1.Temperature > wt2.Temperature

and difference between dates in days of 1 (yesterday):

TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;

• Just for sharing. Interesting function `subdate()`.

``````SELECT w1.Id FROM Weather w1, Weather w2
WHERE subdate(w1.Date, 1)=w2.Date AND w1.Temperature>w2.Temperature``````

• select a.id from Weather a inner join Weather b on datediff(a.Date, b.Date) = 1 and a.Temperature>b.Temperature;

• syntactic sugar version for your reference.

``````select cur.Id
from Weather cur
join Weather prev
on prev.Date + interval 1 day = cur.Date
where cur.Temperature > prev.Temperature
``````

• the problem is when you put field in function you can't use index

• ``````select w1.Id
from Weather w1
where w1.Temperature > (select w2.Temperature from Weather w2 where DATE_SUB(w1.Date,INTERVAL 1 DAY) = w2.Date);
``````

• My solution:

select w1.Id
from Weather w1 left join Weather w2
on w1.date = w2.date + interval 1 day
where w1.Temperature > w2.Temperature

• ``````mysql> select * FROM Weather;
+----+------------+-------------+
| Id | Date       | Temperature |
+----+------------+-------------+
|  1 | 2015-01-01 |          10 |
|  2 | 2015-01-02 |          25 |
|  3 | 2015-01-03 |          20 |
|  4 | 2015-01-04 |          30 |
+----+------------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT w2.Id, w2.Date, w1.Id, w1.Date
-> FROM Weather w1,Weather w2
-> WHERE w2.Date = DATE_ADD(w1.Date, INTERVAL 1 DAY);
+----+------------+----+------------+
| Id | Date       | Id | Date       |
+----+------------+----+------------+
|  2 | 2015-01-02 |  1 | 2015-01-01 |
|  3 | 2015-01-03 |  2 | 2015-01-02 |
|  4 | 2015-01-04 |  3 | 2015-01-03 |
+----+------------+----+------------+
3 rows in set (0.00 sec)

mysql> SELECT w2.Id, w2.Date, w1.Id, w1.Date  FROM Weather w1,Weather w2 WHERE w2.Date = DATE_ADD(w1.Date, INTERVAL 1 DAY) AND w2.Temperature > w1.Temperature;
+----+------------+----+------------+
| Id | Date       | Id | Date       |
+----+------------+----+------------+
|  2 | 2015-01-02 |  1 | 2015-01-01 |
|  4 | 2015-01-04 |  3 | 2015-01-03 |
+----+------------+----+------------+
2 rows in set (0.01 sec)
``````

so this can work

``````SELECT w2.Id  FROM Weather w1,Weather w2
WHERE w2.Date = DATE_ADD(w1.Date, INTERVAL 1 DAY)
AND w2.Temperature > w1.Temperature;
``````

• @lvxinwei datediff(a.Date,b.Date)=1 means a.Date is earlier than b.Date , right ? e.g: a.Date = 2016-10-29, b.Date=2016-10-30 . How come it works ?

• ``````select Id
from Weather w
where exists (select 1 from Weather iw where TO_DAYS(w.DATE)-TO_DAYS(iw.DATE)=1 and iw.Temperature < w.Temperature)
``````

• @lalahahaane This solution got "Time Limit Exceeded", just don't know why.

• @fabrizio3 neat solution. However I find lacking of window function in MySQL really cumbersome

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.