WITH RANKING AS (
    SELECT
        AREA_CODE
        , SURVEY_YEAR
        , ROUND(CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100, 1) AS RATIO
        , DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY ROUND(CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100, 1)) AS RANK
    FROM
        HOUSEHOLD_SURVEY
),
LAYOUT AS (
    SELECT
        AREA_CODE
        , CASE WHEN SURVEY_YEAR = 2022 THEN RATIO ELSE 0 END 'RATIO_2022'
        , CASE WHEN SURVEY_YEAR = 2022 THEN RANK  ELSE 0 END 'RANK_2022'
        , CASE WHEN SURVEY_YEAR = 2017 THEN RATIO ELSE 0 END 'RATIO_2017'
        , CASE WHEN SURVEY_YEAR = 2017 THEN RANK  ELSE 0 END 'RANK_2017'
        , CASE WHEN SURVEY_YEAR = 2012 THEN RATIO ELSE 0 END 'RATIO_2012'
        , CASE WHEN SURVEY_YEAR = 2012 THEN RANK  ELSE 0 END 'RANK_2012'
    FROM
        RANKING a
)
SELECT
    a.AREA_CODE              AS CODE
    , AREA_NAME              AS NAME
    , MAX(RANK_2022)         AS '2022_RANK'
    , MAX(RATIO_2022) || '%' AS '2022_RATIO'
    , MAX(RANK_2017)         AS '2017_RANK'
    , MAX(RATIO_2017) || '%' AS '2017_RATIO'
    , MAX(RANK_2012)         AS '2012_RANK'
    , MAX(RATIO_2012) || '%' AS '2012_RATIO'
FROM
    LAYOUT a
    INNER JOIN AREA b ON
        a.AREA_CODE = b.AREA_CODE
GROUP BY
    a.AREA_CODE
ORDER BY
    MAX(RATIO_2022)
    , a.AREA_CODE DESC
;