LeetCode 197. Rising Temperature SQL Solution

Problem

LeetCode SQL Problem

197. Rising Temperature

Weather table

id	recordDate	Temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30
5	2015-01-05	30
6	2015-01-06	29

Solution - Self Join Weather Table

MySQL

Rising Temperature

-- Self Join Weather table. Alias 'P' for Previous Day. Alias 'C' for Current Day.
SELECT C.id
FROM Weather AS P
INNER JOIN Weather AS C ON DATE_ADD(P.recordDate, INTERVAL 1 DAY) = C.recordDate
	AND C.Temperature > P.Temperature

TSQL

Rising Temperature

-- Self Join Weather table. Alias 'P' for Previous Day. Alias 'C' for Current Day.
SELECT C.id
FROM Weather AS P
INNER JOIN Weather AS C ON DATEADD(DAY, 1, P.recordDate) = C.recordDate
	AND C.Temperature > P.Temperature

Query Output

id
2
4

Solution - Lag() Window Function

MySQL

Rising Temperature

-- Use Lag() window function to get the previous day's temperature
WITH WeatherPlusPrevDayTemp
AS (
	SELECT *
		,lag(Temperature) OVER (
			ORDER BY recordDate
			) AS PrevDayTemperature
	FROM Weather
	)
SELECT id
FROM WeatherPlusPrevDayTemp
-- Find all dates' id with higher temperature compared to yesterday
WHERE Temperature > PrevDayTemperature

TSQL

Rising Temperature

-- Use Lag() window function to get the previous day's temperature
WITH WeatherPlusPrevDayTemp
AS (
	SELECT *
		,lag(Temperature) OVER (
			ORDER BY recordDate
			) AS PrevDayTemperature
	FROM Weather
	)
SELECT id
FROM WeatherPlusPrevDayTemp
-- Find all dates' id with higher temperature compared to yesterday
WHERE Temperature > PrevDayTemperature

Query Output

id
2
4