WITH t2022 AS (
    SELECT
        AREA_CODE AS CODE,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) || "%" AS R2022
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2022
),
c2022 AS (
    SELECT
        CODE,
        R2022,
        rank() over(
            ORDER BY
                R2022 ASC
        ) AS RNK2022
    FROM
        t2022
),
t2017 AS (
    SELECT
        AREA_CODE AS CODE,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) || "%" AS R2017
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2017
),
c2017 AS (
    SELECT
        CODE,
        R2017,
        rank() over(
            ORDER BY
                R2017 ASC
        ) AS RNK2017
    FROM
        t2017
),
t2012 AS (
    SELECT
        AREA_CODE AS CODE,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) || "%" AS R2012
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2012
),
c2012 AS (
    SELECT
        CODE,
        R2012,
        rank() over(
            ORDER BY
                R2012 ASC
        ) AS RNK2012
    FROM
        t2012
)
SELECT
    c2022.CODE AS CODE,
    AREA_NAME AS "NAME",
    RNK2022 AS "2022_RANK",
    R2022 AS "2022_RATIO",
    RNK2017 AS "2017_RANK",
    R2017 AS "2017_RATIO",
    RNK2012 AS "2012_RANK",
    R2012 AS "2012_RATIO"
FROM
    c2022
    INNER JOIN c2017 ON c2022.CODE = c2017.CODE
    INNER JOIN c2012 ON c2022.CODE = c2012.CODE
    INNER JOIN AREA ON c2022.CODE = AREA.AREA_CODE
ORDER BY
    R2022 ASC,
    CODE DESC