LeetCode 197: Rising Temperature [SQL]

Solution and walkthrough of leetcode database problem 197: Rising Temperature. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.
Link to the problem: leetcode.com/p...

[@rskmahesh3531]

Dude this is the best explanation for this question on entire youtube.

[@ft_peakhd2921]

Hi Frederik, Out of no where your channel popped up in my feed. Now, i can't stop watching your videos. Your explanation is very understandable and your approach to every problem is very good. You deserve a subscribe. Thank you, keep making more videos.

[@lakshmanank1950]

Hi Frederik, your different approach to problem-solving is very useful. thanks.

[@summer_in_california]

THIS IS THE BEST SOLUTION EVER. VERY SIMPLE AND CLEAR! THANK YOU SOOOO MUCHHHHH

[@alibhaiacademy2981]

My solution but still 33.73% WITH temp AS ( SELECT id, temperature, recorddate, LAG(temperature, 1) OVER(ORDER BY recorddate) AS previous_temperature, LAG(recorddate, 1) OVER(ORDER BY recorddate) AS previous_date FROM weather ) SELECT id FROM temp WHERE DATEDIFF(recorddate, previous_date) = 1 AND temperature > previous_temperature

[@rohitekka2674]

Thanks alot for enlightening us with different approaches. Great explanation and video as always

[@RahmatKhanZai89]

حیرانوونکی کار. دا زما سره د ستونزې په پوهیدو کې مرسته وکړه. همدارنګه دا یو ډیر ساده حل دی.

[@frederikmuller]

ستاسو د نظر څخه مننه

[@spyboy0076]

u got a sub man. thanks

[@inzamam483]

WHY THIS SOLUTION is giving an error SELECT id FROM (SELECT *,LEAD(temperature,1,0 ) OVER(ORDER BY id ) as tem FROM Weather) AS wea WHERE tem < temperature

[@inzamam483]

WHY THIS SOLUTION is giving an error SELECT id FROM (SELECT id, recordDate, temperature, LEAD(temperature,1,0 ) OVER(ORDER BY id ) as tem, LAG(RECORDDATE,1,0) OVER (ORDER BY RECORDDATE) AS D1, RECORDDATE AS D2 FROM Weather) AS wea WHERE tem < temperature AND TO_DAYS(wea.D2)-TO_DAYS(wea.D1)=1

[@abhinavkumar6584]

why didn't we do it via id difference?

[@karthikbs8457]

In MS SQL the DATEDIFF subtracts like seconddate--firstdate. In Mysql its the opposite

[@frederikmuller]

interesting 🤔

[@karthikbs8457]

@@frederikmuller Fredrick what are all the concepts in SQL which are termed as Advanced SQL?

[@frederikmuller]

@@karthikbs8457 I'd say window functions, CTEs, HAVING, complex OUTER JOINs, user defined variables

[@SchreiEs]

SQL Server solutions: --Method 1 with cte1 as ( select * ,lag(temperature,1) over (order by recordDate) as previous_temperature from weather ) select id from cte1 where temperature > previous_temperature; --Method 2 select curr.id from weather curr join weather prev on curr.id = prev.id + 1 where curr.temperature > prev.temperature; --Method 3 select curr.id from weather curr join weather prev on datediff(day,curr.recordDate,prev.recordDate) = -1 where curr.temperature > prev.temperature;

[@mickyman753]

select p1.id from Weather p1,Weather p2 where p1.temperature>p2.temperature and DateDiff(p1.recordDate,p2.recordDate)=1;

[@shrutisugandhi1172]

sheer genius!

[@viveksivalingam9181]

Hi Fredrik What if the dates are not consecutive ? LeetCode uses dates that are not consecutive as a test case when you hit 'Submit' tx

[@frederikmuller]

Hi Vivek, the question specifically states: Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday). However, if you were to allow for gaps in the data, you could use window functions and lag/lead functions to find the row above/below the current date and compare to it. You could also join on w2.recordDate > w1.recordDate and then select MIN(w2.recordDate) to get the smallest date that is bigger than the one you compare to (make sure you also group by w1.recordDate then). Hope this explanation wasn't too confusing.

[@ketanverma7839]

can lead or lag be used in this type of question ?

[@frederikmuller]

yes, absolutely, you could compare temperature to the LAG of temperature to solve this question

[@AbhishekSharma-hy4nl]

But how to do this on sql server. DATEDIFF is giving error in sql server.

[@frederikmuller]

You have to specify an interval in the function in SQL Server, it would be DATEDIFF (day, w1.recordDate, w2.recordDate) then. In MySQL it's in days by default.

[@AbhishekSharma-hy4nl]

@@frederikmuller thnx it worked.👍

[@frederikmuller]

Great! There are slight differences between SQL dialects but usually everything is available everywhere. Functions working slightly differently here is a good example.

[@venkatmanchikalapudi6448]

select w1.id from weather w1 join weather w2 on w1.id+1 = w2.id and w1.temperature > w2.temperature;. hi why does this doesn't work?

[@ishitvasingh9902]

you have to write w2.temperature > w1.temperature and select w2.id that is why you have to go step by step firstly when you join see the result then apply conditions

[@leasunsun]

select w2.id from weather w1 join weather w2 on w1.id+1 = w2.id and w1.temperature < w2.temperature;

[@mickyman753]

as the id don't have somthing to do with dates , you can have a id=3 of tuesday , and id=2 for wednesday , we strictly have to previous day , which can only be identified by dates

[@maheshodedra8609]

Hi Frederik, Thanks for the great explanation, would you please look into my solution and let me know if any issues? select w2.id from weather w1 join weather w2 on w1.id+1 = w2.id and w2.temprature > w1.temprature Looking forward to your response. :-) Thanks Mahesh