WITH RANKING2022 AS (
    SELECT
        2022
        , a.AREA_CODE AS CODE
        , AREA_NAME   AS NAME
        , ROUND(CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100, 2) AS RATIO
        , DENSE_RANK() OVER (ORDER BY CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100 DESC) AS RANK
    FROM
        HOUSEHOLD_SURVEY a
        INNER JOIN AREA b ON
            a.AREA_CODE = b.AREA_CODE
    WHERE
        SURVEY_YEAR = 2022
),
RANKING2017 AS (
    SELECT
        2017
        , a.AREA_CODE AS CODE
        , AREA_NAME   AS NAME
        , ROUND(CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100, 2) AS RATIO
        , DENSE_RANK() OVER (ORDER BY CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100 DESC) AS RANK
    FROM
        HOUSEHOLD_SURVEY a
        INNER JOIN AREA b ON
            a.AREA_CODE = b.AREA_CODE
    WHERE
        SURVEY_YEAR = 2017
),
RANKING2012 AS (
    SELECT
        2012
        , a.AREA_CODE AS CODE
        , AREA_NAME   AS NAME
        , ROUND(CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100, 2) AS RATIO
        , DENSE_RANK() OVER (ORDER BY CAST(FOOD_EXP AS FLOAT) / CONSUMPTION_EXP * 100 DESC) AS RANK
    FROM
        HOUSEHOLD_SURVEY a
        INNER JOIN AREA b ON
            a.AREA_CODE = b.AREA_CODE
    WHERE
        SURVEY_YEAR = 2012
)
SELECT
    a.CODE
    , a.NAME
    , a.RANK  AS '2022_RANK'
    , a.RATIO AS '2022_RATIO'
    , b.RANK  AS '2017_RANK'
    , b.RATIO AS '2017_RATIO'
    , c.RANK  AS '2012_RANK'
    , c.RATIO AS '2012_RATIO'

FROM
    RANKING2022 a
    LEFT JOIN RANKING2017 b ON
        a.CODE = b.CODE
    LEFT JOIN RANKING2012 c ON
        a.CODE = c.CODE
ORDER BY
    a.CODE DESC
;