WITH RATIOS_BY_AREA AS (
    WITH RATIOS AS (
        SELECT
            SURVEY_YEAR,
            AREA_CODE,
            ROUND(FOOD_EXP * 100.0 / CONSUMPTION_EXP, 1) AS RATIO
        FROM
            HOUSEHOLD_SURVEY
        WHERE
            SURVEY_YEAR IN (2022, 2017, 2012)
    )
    SELECT
        AREA_CODE,
        MAX(
            CASE
                SURVEY_YEAR
                WHEN 2022 THEN RATIO
                ELSE NULL
            END
        ) AS '_2022_RATIO',
        MAX(
            CASE
                SURVEY_YEAR
                WHEN 2017 THEN RATIO
                ELSE NULL
            END
        ) AS '_2017_RATIO',
        MAX(
            CASE
                SURVEY_YEAR
                WHEN 2012 THEN RATIO
                ELSE NULL
            END
        ) AS '_2012_RATIO'
    FROM
        RATIOS
    GROUP BY
        AREA_CODE
)
SELECT
    AREA_CODE AS CODE,
    AREA_NAME AS NAME,
    DENSE_RANK () OVER (
        ORDER BY
            _2022_RATIO ASC
    ) AS '2022_RANK',
    _2022_RATIO || '%' AS '2022_RATIO',
    DENSE_RANK () OVER (
        ORDER BY
            _2017_RATIO ASC
    ) AS '2017_RANK',
    _2017_RATIO || '%' AS '2017_RATIO',
    DENSE_RANK () OVER (
        ORDER BY
            _2012_RATIO ASC
    ) AS '2012_RANK',
    _2012_RATIO || '%' AS '2012_RATIO'
FROM
    RATIOS_BY_AREA
    INNER JOIN AREA USING (AREA_CODE)
ORDER BY
    _2022_RATIO ASC,
    AREA_CODE DESC