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
)
,
Pre_LAST AS(
SELECT
    Pre_2022.CODE,
    Pre_2022.NAME,
    DENSE_RANK() OVER(ORDER BY Per_2022 ASC) AS a_2022_RANK,
    CAST(Per_2022 as str) ||'%'  AS a_2022_RATIO,
    DENSE_RANK() OVER(ORDER BY Per_2017 ASC) AS a_2017_RANK,
    CAST(Per_2017 as str) || '%' AS a_2017_RATIO,
    DENSE_RANK() OVER(ORDER BY Per_2012 ASC) AS a_2012_RANK,
    CAST(Per_2012 as str) || '%' AS a_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
    a_2022_RANK asc,
    Pre_2022.CODE DESC
)
SELECT
    CODE,
    NAME,
    a_2022_RANK AS '2022_RANK',
    a_2022_RATIO AS '2022_RATIO',
    a_2017_RANK AS '2017_RANK',
    a_2017_RATIO AS '2017_RATIO',
    a_2012_RANK AS '2012_RANK',
    a_2012_RATIO AS '2012_RATIO'
FROM
    Pre_LAST
GROUP BY
    CODE
ORDER BY
    a_2022_RANK,
    CODE DESC
LIMIT
    5