WITH TEMP AS(
SELECT
    H.AREA_CODE AS CODE
    ,AREA_NAME AS NAME
    ,ROUND(CAST(SUM(CASE WHEN SURVEY_YEAR = 2022 THEN FOOD_EXP ELSE 0 END) AS FLOAT)
    /SUM(CASE WHEN SURVEY_YEAR = 2022 THEN CONSUMPTION_EXP ELSE 0 END) 
    * 100,1) AS 'R22'
    ,ROUND(CAST(SUM(CASE WHEN SURVEY_YEAR = 2017 THEN FOOD_EXP ELSE 0 END) AS FLOAT)
    /SUM(CASE WHEN SURVEY_YEAR = 2017 THEN CONSUMPTION_EXP ELSE 0 END) 
    * 100,1) AS 'R17'    
    ,ROUND(CAST(SUM(CASE WHEN SURVEY_YEAR = 2012 THEN FOOD_EXP ELSE 0 END) AS FLOAT)
    /SUM(CASE WHEN SURVEY_YEAR = 2012 THEN CONSUMPTION_EXP ELSE 0 END) 
    * 100,1) AS 'R12'    
FROM
    HOUSEHOLD_SURVEY H
    LEFT JOIN AREA A
    ON H.AREA_CODE = A.AREA_CODE
GROUP BY
    H.AREA_CODE
)
SELECT
    CODE
    ,NAME
    ,RANK() OVER (ORDER BY R22) AS '2022_RANK'
    ,R22 AS '2022_RATIO'
    ,RANK() OVER (ORDER BY R17) AS '2017_RANK'
    ,R22 AS '2017_RATIO'
    ,RANK() OVER (ORDER BY R12) AS '2012_RANK'
    ,R22 AS '2012_RATIO'
FROM
    TEMP
ORDER BY 
    3 ASC
    ,1 DESC