BigQuery SQL to get the Adjusted Closing Price for the Last Trading Day in every Quarter (via dimension date table)

ASX_200_Cumulative_Percentage_Growth_By_Quarter.md

BigQuery SQL to get the Adjusted Closing Price for the Last Trading Day in every Quarter (via dimension date table)

WITH 
ASX_DATA AS (
  SELECT 
    'ASX 200 (XJO)' AS Index
    ,DT.Date
    ,DT.Month
    ,DT.Year
    ,DT.Quarter
    ,DT.EndOfQuarter
    ,DT.Workday_YN
    ,A.Adj_Close AS ADJUSTED_CLOSE
    --,Volume
    ,LAG(A.Adj_Close) OVER (ORDER BY DT.Date ASC) AS LAG_ADJUSTED_CLOSE
    ,ROW_NUMBER() OVER (PARTITION BY DT.Year, DT.Quarter ORDER BY DT.Date DESC) AS RNK
  FROM transformation.ASX_AXJO_200_INDEX_TRF A
  LEFT JOIN DWH.DIM_DATE DT
  ON A.Date = DT.Date
  WHERE 1=1
  AND Adj_Close IS NOT NULL
  AND Adj_Close != 0
)
,QUARTER_ASX_DATA AS (
SELECT
  Index
  ,Date AS Last_Working_Day
  ,EndOfQuarter
  ,ADJUSTED_CLOSE
  ,LAG_ADJUSTED_CLOSE
  ,ROW_NUMBER() OVER (ORDER BY Date ASC) AS QTR_RNK
FROM ASX_DATA
WHERE 1=1
AND RNK = 1
)
,ANCHOR_PRICE AS (
  SELECT Index, ADJUSTED_CLOSE AS ANCHOR_PRICE
  FROM QUARTER_ASX_DATA
  WHERE QTR_RNK = 1
)
SELECT
  Q.Index
  ,Q.Last_Working_Day
  ,Q.EndOfQuarter
  ,Q.ADJUSTED_CLOSE
  ,A.ANCHOR_PRICE
  ,Q.LAG_ADJUSTED_CLOSE
  ,ROUND((Q.ADJUSTED_CLOSE - Q.LAG_ADJUSTED_CLOSE)/Q.LAG_ADJUSTED_CLOSE, 2) AS PERCENTAGE_CHANGE
  ,ROUND((Q.ADJUSTED_CLOSE - A.ANCHOR_PRICE)/A.ANCHOR_PRICE, 2) AS CUMULATIVE_PERCENTAGE_CHANGE
FROM QUARTER_ASX_DATA Q
LEFT JOIN ANCHOR_PRICE A
ON Q.Index = A.Index
;