03_analyticalFunctions.sql

-- Analytical functions (Other)
SELECT *,
	LEAD(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) leadValue,
	LEAD(t1.Value, 1, 0) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) leadValueZero,
	FIRST_VALUE(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) firstValue,
	LAST_VALUE(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		) lastValue
FROM Medium.dbo.HPM09_202112 AS t1
WHERE t1.Value > 0  
	AND t1.Date BETWEEN '2020-01-01' AND '2020-06-01'