Leetcode Rising Temperature problem solution

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

coding problems solutions