WITH Pre AS(
SELECT
    H.AREA_CODE AS CODE,
    A.AREA_NAME AS NAME,
    ROUND(
        100.0 * 	
                SUM(CASE WHEN H.SURVEY_YEAR = 2022 THEN FOOD_EXP END)
			 / 
				SUM(CASE WHEN H.SURVEY_YEAR = 2022 THEN CONSUMPTION_EXP END),
		1) AS Per_2022,
	ROUND(
        100.0 * 	
                SUM(CASE WHEN H.SURVEY_YEAR = 2017 THEN FOOD_EXP END)
			 / 
				SUM(CASE WHEN H.SURVEY_YEAR = 2017 THEN CONSUMPTION_EXP END),
		1) AS Per_2017,
	ROUND(
        100.0 * 	
                SUM(CASE WHEN H.SURVEY_YEAR = 2012 THEN FOOD_EXP END)
			 / 
				SUM(CASE WHEN H.SURVEY_YEAR = 2012 THEN CONSUMPTION_EXP END),
		1) AS Per_2012
FROM
    HOUSEHOLD_SURVEY AS H
JOIN
    AREA AS A
ON
    H.AREA_CODE = A.AREA_CODE
GROUP BY
    CODE
)
SELECT
    CODE,
    NAME,
    DENSE_RANK() OVER(ORDER BY Per_2022 ASC) AS '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
GROUP BY
    CODE
ORDER BY
    DENSE_RANK() OVER(ORDER BY Per_2022 ASC),
    CODE DESC