In this Leetcode Rising Temperature problem solution, we need to write an SQL query to find all dates' IDs with higher temperatures compared to their previous dates (yesterday). Return the result table in any order.
First Problem solution.
select id from ( SELECT id, case when lag(Temperature) over(order by recordDate) < Temperature and lag(recordDate) over (order by RecordDate) = recordDate - interval '1' day THEN 1 ELSE 0 END SEL FROM weather )x where sel = 1
Second Problem solution.
SELECT Id FROM ( SELECT Id, (Temperature - LAG(Temperature) OVER (ORDER BY RecordDate)) as temp_diff, DATEDIFF(RecordDate, LAG(RecordDate) OVER (ORDER BY RecordDate)) as date_diff FROM Weather ) as innerQ WHERE temp_diff > 0 and date_diff = 1
Third Problem solution.
SELECT w.id FROM Weather w JOIN Weather p ON w.recordDate = DATE_ADD(p.recordDate, INTERVAL 1 DAY) AND w.Temperature > p.Temperature
0 Comments