WITH T AS (
    SELECT
        AREA_CODE CODE,
        SURVEY_YEAR Y,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) || "%" RATIO,
        dense_rank() over(
            PARTITION by SURVEY_YEAR
            ORDER BY
                round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ) RANK
    FROM
        HOUSEHOLD_SURVEY
    GROUP BY
        SURVEY_YEAR,
        AREA_CODE
)
SELECT
    AREA_CODE CODE,
    AREA_NAME 'NAME',
    t2022.RANK '2022_RANK',
    t2022.RATIO '2022_RATIO',
    t2017.RANK '2017_RANK',
    t2017.RATIO '2017_RATIO',
    t2012.RANK '2012_RANK',
    t2012.RATIO '2012_RATIO'
FROM
    AREA
    INNER JOIN T AS t2022 ON AREA.AREA_CODE = t2022.CODE
    AND t2022.Y = 2022
    INNER JOIN T AS t2017 ON AREA.AREA_CODE = t2017.CODE
    AND t2017.Y = 2017
    INNER JOIN T AS t2012 ON AREA.AREA_CODE = t2012.CODE
    AND t2012.Y = 2012
ORDER BY
    t2022.RANK ASC,
    AREA_CODE DESC