WITH
    table2022 AS (
        SELECT
            AREA_CODE,
            ROUND(
                CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100.0,
                1
            ) AS RATIO,
            DENSE_RANK() OVER (
                ORDER BY
                    ROUND(CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100, 1) ASC
            ) AS RANK
        FROM
            HOUSEHOLD_SURVEY
        WHERE
            SURVEY_YEAR = 2022
    ),
    table2017 AS (
        SELECT
            AREA_CODE,
            ROUND(
                CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100.0,
                1
            ) AS RATIO,
            DENSE_RANK() OVER (
                ORDER BY
                    ROUND(CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100, 1) ASC
            ) AS RANK
        FROM
            HOUSEHOLD_SURVEY
        WHERE
            SURVEY_YEAR = 2017
    ),
    table2012 AS (
        SELECT
            AREA_CODE,
            ROUND(
                CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100.0,
                1
            ) AS RATIO,
            DENSE_RANK() OVER (
                ORDER BY
                    ROUND(CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100, 1) ASC
            ) AS RANK
        FROM
            HOUSEHOLD_SURVEY
        WHERE
            SURVEY_YEAR = 2012
    )
SELECT
    t2022.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
    table2022 AS t2022
    INNER JOIN table2017 AS t2017 ON t2022.AREA_CODE = t2017.AREA_CODE
    INNER JOIN table2012 AS t2012 ON t2022.AREA_CODE = t2012.AREA_CODE
    INNER JOIN AREA ON t2022.AREA_CODE = AREA.AREA_CODE
ORDER BY
    t2022.RANK ASC,
    t2022.AREA_CODE DESC