WITH HS2022 AS
(
    SELECT
        SURVEY_YEAR,
        AREA_CODE,
        ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) AS RATIO,
        DENSE_RANK() OVER (ORDER BY ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) ASC) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR=2022
),
HS2017 AS
(
    SELECT
        SURVEY_YEAR,
        AREA_CODE,
        ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) AS RATIO,
        DENSE_RANK() OVER (ORDER BY ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) ASC) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR=2017
),
HS2012 AS
(
    SELECT
        SURVEY_YEAR,
        AREA_CODE,
        ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) AS RATIO,
        DENSE_RANK() OVER (ORDER BY ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) ASC) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR=2012
)
SELECT
    HS2022.AREA_CODE AS CODE,
    A.AREA_NAME AS NAME,
    HS2022.RANK AS '2022_RANK',
    HS2022.RATIO || '%' AS '2022_RATIO',
    HS2017.RANK AS '2017_RANK',
    HS2017.RATIO || '%' AS '2017_RATIO',
    HS2012.RANK AS '2012_RANK',
    HS2012.RATIO || '%' AS '2012_RATIO'
FROM
    HS2022
    INNER JOIN
        HS2017
        ON
            HS2017.AREA_CODE = HS2022.AREA_CODE
    INNER JOIN
        HS2012
        ON
            HS2012.AREA_CODE = HS2022.AREA_CODE
    LEFT OUTER JOIN
        AREA AS A
        ON
            A.AREA_CODE = HS2022.AREA_CODE
ORDER BY
    HS2022.RANK ASC,
    HS2022.AREA_CODE DESC