02_lagFunction.sql

-- Analytical functions (LAG)
SELECT *,
	LAG(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) lagValue,
	LAG(t1.Value, 3) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) lagValue3,
	COALESCE(
		LAG(t1.Value) 
			OVER(
			PARTITION BY t1.Area, t1.Property_type
			ORDER BY t1.Date
			)
		, 0) lagValueZero,
	LAG(t1.Value, 3, 0) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) lagValue3
FROM Medium.dbo.HPM09_202112 AS t1
WHERE t1.Value > 0  
	AND t1.Date BETWEEN '2020-01-01' AND '2020-06-01'