WITH RATIO AS (
SELECT
    AREA_CODE,
    CASE
        WHEN SURVEY_YEAR = 2022 THEN ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ELSE 0
    END AS RATIO_2022,
    CASE
        WHEN SURVEY_YEAR = 2017 THEN ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ELSE 0
    END AS RATIO_2017,
    CASE
        WHEN SURVEY_YEAR = 2012 THEN ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ELSE 0
    END AS RATIO_2012
FROM
    HOUSEHOLD_SURVEY
), RANK_2022 AS (
SELECT
    AREA_CODE,
    MAX(RATIO_2022) AS RATIO_2022,
    DENSE_RANK() OVER (ORDER BY RATIO_2022) AS RANK_2022
FROM
    RATIO
GROUP BY
    1
), RANK_2017 AS (
SELECT
    AREA_CODE,
    MAX(RATIO_2017) AS RATIO_2017,
    DENSE_RANK() OVER (ORDER BY RATIO_2017) AS RANK_2017
FROM
    RATIO
GROUP BY
    1
), RANK_2012 AS (
SELECT
    AREA_CODE,
    MAX(RATIO_2012) AS RATIO_2012,
    DENSE_RANK() OVER (ORDER BY RATIO_2012) AS RANK_2012
FROM
    RATIO
GROUP BY
    1
)
SELECT
    AREA_CODE AS CODE,
    AREA_NAME AS NAME,
    RANK_2022 AS '2022_RANK',
    RATIO_2022 || '%' AS '2022_RATIO',
    RANK_2017 AS '2017_RANK',
    RATIO_2017 || '%' AS '2017_RATIO',
    RANK_2012 AS '2012_RANK',
    RATIO_2012 || '%' AS '2012_RATIO'
FROM
    RANK_2022
INNER JOIN RANK_2017 USING(AREA_CODE)
INNER JOIN RANK_2012 USING(AREA_CODE)
INNER JOIN AREA USING(AREA_CODE)
ORDER BY
    3, 1 DESC