WITH MAIN_2022 AS(
    SELECT
        AREA_CODE
        ,ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP,1) AS RATIO
        ,DENSE_RANK() OVER(
            ORDER BY
                ROUND(
                    CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100.0
                    , 1
                )
        ) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2022
)
,SUB_2017 AS(
    SELECT
        AREA_CODE
        ,ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP,1) AS RATIO
        ,DENSE_RANK() OVER(
            ORDER BY
                ROUND(
                    CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100.0
                    , 1
                )
        ) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2017
)
,SUB_2012 AS(
    SELECT
        AREA_CODE
        ,ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP,1) AS RATIO
        ,DENSE_RANK() OVER(
            ORDER BY 
                ROUND(
                    CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100.0
                    , 1
                )
        ) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2012
)
SELECT
    M22.AREA_CODE AS CODE
    ,AREA_NAME AS NAME
    ,M22.RANK AS '2022_RANK'
    ,M22.RATIO || '%' AS '2022_RATIO'
    ,S17.RANK AS '2017_RANK'
    ,S17.RATIO || '%' AS '2017_RATIO'
    ,S12.RANK AS '2012_RANK'
    ,S12.RATIO || '%' AS '2012_RATIO'
FROM
    MAIN_2022 AS M22
     INNER JOIN SUB_2017 AS S17
        ON S17.AREA_CODE = M22.AREA_CODE
     INNER JOIN SUB_2012 AS S12
        ON S12.AREA_CODE = M22.AREA_CODE
    INNER JOIN AREA
        ON AREA.AREA_CODE = M22.AREA_CODE
ORDER BY
    M22.RANK
    ,M22.AREA_CODE DESC