05_timeSeriesFunctions.sql

-- Time Series functions
SELECT *,
	AVG(t1.Value) 
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
		) sma3,
	LAG(t1.Value, 3, 0)
		OVER(
		PARTITION BY t1.Area, t1.Property_type
		ORDER BY t1.Date
		) lagValue3,
	(t1.Value / 
		LAG(t1.Value, 3)
			OVER(
			PARTITION BY t1.Area, t1.Property_type
			ORDER BY t1.Date
			)
	) - 1 AS pctChange3m
FROM Medium.dbo.HPM09_202112 AS t1
WHERE t1.Value > 0  
	AND t1.Date BETWEEN '2020-01-01' AND '2020-06-01'