LeetCode 197 "Rising Temperature" Amazon & Adobe Interview SQL Question with Detailed Explanation

[@walk_with_mohan]

select w1.id from weather w1 ,weather w2
where datediff(w1.recordDate,w2.recordDate) = 1
and w1.temperature > w2.temperature;

[@january6707]

EXPLAIN

[@aaravkumarsingh4018]

select w1.id from weather w1 ,weather w2 where datediff(w1.recordDate,w2.recordDate)=1
AND w1.temperature > w2.temperature;

[@juveneilslife7623]

Thanks bro , for the simplest explanation .

[@kshirod_behera]

Oracle Solution (Using Join)
Select a.id
from weather a join weather b
on a.recordDate-b.recordDate=1
and a.temperature > b.temperature;

[@rajatverma2776]

It worked. but 13/14 test cases passed, so it showing up as wrong answer.

[@justcodeitbro1312]

appreciate your work bro please continue this awesome work

[@mickyman753]

select distinct a.id from weather a join weather b
on
a.temperature>b.temperature
and a.recorddate

[@dileepn2479]

This is much easier
select b.id from weather a , weather b where a.temparature< b.temparature and a.recordDate+1=b.recordDate;

[@TuringTested01]

doesnt work, it will fail on test cases where the dates are at month end or year end

[@balamira297]

Can we use self join here?

[@Anshukumar-ep6rn]

yes, we can use self join here.

[@neelshah8247]

Amazing!

[@sindhujajittuka9020]

Can anyone explain

[@abhinavtiwari6186]

WITH CTE AS(
select id, temperature, lag(temperature, 1) over( order by id ) as prev_temp
from weather
)
SELECT CTE.id FROM CTE WHERE temperature > cte.prev_temp ;
What's wrong with this?

[@hoonoh676]

There's no corelation between id and date; therefore, Order by should be on Date not ID, and there might be some date without any temperature data.

[@avadhootmuli1842]

Did using self join
select w1.id
from weather w1
left join weather w2
on w1.recordDate= w2.recordDate+1
where (w1.temperature-w2.temperature) > 0
;