WITH T AS(
    SELECT
        HOUSEHOLD_SURVEY.AREA_CODE AS CODE,
        AREA.AREA_NAME AS NAME,
        ROUND(100.0 * SUM(
            CASE SURVEY_YEAR
                WHEN 2022 THEN FOOD_EXP
                ELSE 0
            END
        ) / SUM(
            CASE SURVEY_YEAR
                WHEN 2022 THEN CONSUMPTION_EXP
                ELSE 0
            END
        ), 1) AS "2022_RATIO",
        ROUND(100.0 * SUM(
            CASE SURVEY_YEAR
                WHEN 2017 THEN FOOD_EXP
                ELSE 0
            END
        ) / SUM(
            CASE SURVEY_YEAR
                WHEN 2017 THEN CONSUMPTION_EXP
                ELSE 0
            END
        ), 1) AS "2017_RATIO",
        ROUND(100.0 * SUM(
            CASE SURVEY_YEAR
                WHEN 2012 THEN FOOD_EXP
                ELSE 0
            END
        ) / SUM(
            CASE SURVEY_YEAR
                WHEN 2012 THEN CONSUMPTION_EXP
                ELSE 0
            END
        ), 1) AS "2012_RATIO"
    FROM
        HOUSEHOLD_SURVEY
        INNER JOIN
            AREA ON HOUSEHOLD_SURVEY.AREA_CODE = AREA.AREA_CODE
    GROUP BY
        HOUSEHOLD_SURVEY.AREA_CODE
)
SELECT
    CODE,
    NAME,
    RANK() OVER(ORDER BY "2022_RATIO" ASC) AS "2022_RANK",
    "2022_RATIO" || '%' AS "2022_RATIO",
    RANK() OVER(ORDER BY "2017_RATIO" ASC) AS "2017_RANK",
    "2017_RATIO" || '%' AS "2017_RATIO",
    RANK() OVER(ORDER BY "2012_RATIO" ASC) AS "2012_RANK",
    "2012_RATIO" || '%' AS "2012_RATIO"
FROM
    T
ORDER BY
    "2022_RANK" ASC,
    CODE DESC