WITH Pre_2022 AS(
SELECT
    H.AREA_CODE AS CODE,
    A.AREA_NAME AS NAME,
    CONSUMPTION_EXP,
    FOOD_EXP,
    ROUND(100 * CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP as real),1) AS Per_2022
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
WHERE
    SURVEY_YEAR = 2022
GROUP BY
    CODE
),
Pre_2017 AS(
SELECT
    H.AREA_CODE AS CODE,
    A.AREA_NAME AS NAME,
    CONSUMPTION_EXP,
    FOOD_EXP,
    ROUND(100 * CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP as real),1) AS Per_2017
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
WHERE
    SURVEY_YEAR = 2017
GROUP BY
    CODE
),
Pre_2012 AS (
SELECT
    H.AREA_CODE AS CODE,
    A.AREA_NAME AS NAME,
    CONSUMPTION_EXP,
    FOOD_EXP,
    ROUND(100 * CAST(FOOD_EXP AS real) / CAST(CONSUMPTION_EXP as real),1) AS Per_2012
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
WHERE
    SURVEY_YEAR = 2012
GROUP BY
    CODE
)
SELECT
    Pre_2022.CODE AS CODE,
    Pre_2022.NAME AS NAME,
    DENSE_RANK() OVER(ORDER BY Per_2022 ASC)  '2022_RANK',
    Per_2022 ||'%'  AS '2022_RATIO',
    DENSE_RANK() OVER(ORDER BY Per_2017 ASC) AS '2017_RANK',
    Per_2017 || '%' AS '2017_RATIO',
    DENSE_RANK() OVER(ORDER BY Per_2012 ASC) AS '2012_RANK',
    Per_2012 || '%' AS '2012_RATIO'
FROM
    Pre_2022
INNER JOIN
    Pre_2017
ON
    Pre_2022.CODE = Pre_2017.CODE
INNER JOIN
    Pre_2012
ON
    Pre_2022.CODE = Pre_2012.CODE
--GROUP BY
--    Pre_2022.CODE
ORDER BY
    DENSE_RANK() OVER(ORDER BY Per_2022 ASC),
    Pre_2022.CODE DESC
LIMIT
    5