WITH pre_2022 AS (
SELECT
    H.AREA_CODE AS CODE,
    AREA_NAME AS NAME,
    DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP AS real) * 100,1)) AS 'RANK',
    ROUND(CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP AS real) * 100,1) || '%' AS'RATIO'
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
WHERE
    SURVEY_YEAR = 2022
),
pre_2017 AS(
SELECT
    H.AREA_CODE AS CODE,
    AREA_NAME AS NAME,
    DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP AS real) * 100,1)) AS 'RANK',
    ROUND(CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP AS real) * 100,1) || '%' AS'RATIO'
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
WHERE
    SURVEY_YEAR = 2017
),
pre_2012 AS(
SELECT
    H.AREA_CODE AS CODE,
    AREA_NAME AS NAME,
    DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP AS real) * 100,1)) AS 'RANK',
    ROUND(CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP AS real) * 100,1) || '%' AS'RATIO'
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
WHERE
    SURVEY_YEAR = 2012
)
SELECT
    pre_2022.CODE AS CODE,
    pre_2022.NAME AS NAME,
    pre_2022.RANK AS '2022_RANK',
    pre_2022.RATIO AS '2022_RATIO',
    pre_2017.RANK AS '2017_RANK',
    pre_2017.RATIO AS '2017_RATIO',
    pre_2012.RANK AS '2012_RANK',
    pre_2012.RATIO AS '2012_RATIO'
FROM
    pre_2022
JOIN
    pre_2017
ON
    pre_2022.CODE = pre_2017.CODE
JOIN
    pre_2012
ON
    pre_2022.CODE = pre_2012.CODE
ORDER BY
    pre_2022.RANK,
    CODE DESC