WITH HS_2022 AS (
    SELECT
        AREA_CODE
        , ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) || '%' AS '2022_RATIO'
        , DENSE_RANK() OVER(
            ORDER BY
                ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) ASC
        ) AS '2022_RANK'
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2022
)
, HS_2017 AS (
    SELECT
        AREA_CODE
        , ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) || '%' AS '2017_RATIO'
        , DENSE_RANK() OVER(
            ORDER BY
                ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) ASC
        ) AS '2017_RANK'
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2017
)
, HS_2012 AS (
    SELECT
        AREA_CODE
        , ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) || '%' AS '2012_RATIO'
        , DENSE_RANK() OVER(
            ORDER BY
                ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) ASC
        ) AS '2012_RANK'
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2012
)
 
SELECT
    HS_2022.AREA_CODE AS CODE
    , AREA.AREA_NAME AS NAME
    , HS_2022."2022_RANK"
    , HS_2022."2022_RATIO"
    , HS_2017."2017_RANK"
    , HS_2017."2017_RATIO"
    , HS_2012."2012_RANK"
    , HS_2012."2012_RATIO"
FROM
    HS_2022
INNER JOIN
    HS_2017 ON HS_2022.AREA_CODE = HS_2017.AREA_CODE
INNER JOIN
    HS_2012 ON HS_2022.AREA_CODE = HS_2012.AREA_CODE
INNER JOIN
    AREA ON HS_2022.AREA_CODE = AREA.AREA_CODE
ORDER BY
    HS_2022."2022_RANK" ASC
    , CODE DESC
;