Cumulative Growth of Australian Residential Property Prices

ABS_RRPI_Cumulative_Growth.md

BigQuery SQL Script to get the rolling and cumulative percentage change for the Average Housing Price by State

WITH PRICES AS (
SELECT 
  Date
  ,State
  ,Residential_Dwelling_Average_Value_AUD AS AVERAGE_HOUSING_PRICE
  ,LAG(Residential_Dwelling_Average_Value_AUD) OVER (PARTITION BY State ORDER BY Date ASC) AS LAG_AVERAGE_HOUSING_PRICE
  ,ROW_NUMBER() OVER (PARTITION BY State ORDER BY Date ASC) AS RNK
FROM transformation.ABS_DWELLING_DATA_TRF
)
,ANCHOR_PRICE AS (
  SELECT
  Date
  ,State
  ,AVERAGE_HOUSING_PRICE AS ANCHOR_PRICE
  FROM PRICES
  WHERE RNK = 1
)
SELECT
  P.Date
  ,P.State
  ,P.AVERAGE_HOUSING_PRICE
  ,A.ANCHOR_PRICE
  ,P.LAG_AVERAGE_HOUSING_PRICE
  ,ROUND((P.AVERAGE_HOUSING_PRICE - P.LAG_AVERAGE_HOUSING_PRICE)/P.LAG_AVERAGE_HOUSING_PRICE, 2) AS PERCENT_CHANGE
  ,ROUND((P.AVERAGE_HOUSING_PRICE - A.ANCHOR_PRICE)/A.ANCHOR_PRICE, 2) AS CUMULATIVE_PERCENT_CHANGE
FROM PRICES P
LEFT JOIN ANCHOR_PRICE A
ON P.State = A.State
ORDER BY State, Date ASC
;