WITH T AS (
    SELECT
        SURVEY_YEAR,
        DENSE_RANK() OVER (
            PARTITION BY SURVEY_YEAR
            ORDER BY ROUND(SUM(CAST(FOOD_EXP AS REAL)) / SUM(CONSUMPTION_EXP) * 100, 1) ASC
        ) AS "RANK",
        AREA_CODE,
        ROUND(SUM(CAST(FOOD_EXP AS REAL)) / SUM(CONSUMPTION_EXP) * 100, 1) || '%' AS "RATIO"
    FROM
        HOUSEHOLD_SURVEY
    GROUP BY
        SURVEY_YEAR,
        AREA_CODE
)
SELECT
    AREA.AREA_CODE AS CODE,
    AREA.AREA_NAME AS NAME,
    T2022."RANK" AS "2022_RANK",
    T2022."RATIO" AS "2022_RATIO",
    T2017."RANK" AS "2017_RANK",
    T2017."RATIO" AS "2017_RATIO",
    T2012."RANK" AS "2012_RANK",
    T2012."RATIO" AS "2012_RATIO"
FROM
    AREA
INNER JOIN
    T AS
    T2022 ON
    T2022.AREA_CODE = AREA.AREA_CODE AND
    T2022.SURVEY_YEAR = 2022
INNER JOIN
    T AS
    T2017 ON
    T2017.AREA_CODE = AREA.AREA_CODE AND
    T2017.SURVEY_YEAR = 2017
INNER JOIN
    T AS
    T2012 ON
    T2012.AREA_CODE = AREA.AREA_CODE AND
    T2012.SURVEY_YEAR = 2012
ORDER BY
    "2022_RANK" ASC,
    AREA.AREA_CODE DESC