WITH tmp AS(
SELECT
	*,
	ROUND(FOOD_EXP*100.0/CONSUMPTION_EXP,1) AS ratio
FROM
	HOUSEHOLD_SURVEY
WHERE
    SURVEY_YEAR IN (2022,2017,2012)
),tmp2 AS(
SELECT
    AREA_CODE AS CODE
    ,survey_year AS year
    ,ratio
    ,DENSE_RANK() over(partition by SURVEY_YEAR order by ratio asc) AS rank
from tmp
)

SELECT
	a.AREA_CODE AS CODE
	,a.AREA_NAME AS NAME
	,t2_1.rank AS '2022_RANK'
	,t2_1.ratio||'%' AS '2022_RATIO'
	,t2_2.rank AS '2017_RANK'
	,t2_2.ratio||'%' AS '2017_RATIO'
	,t2_3.rank AS '2012_RANK'
	,t2_3.ratio||'%' AS '2012_RATIO'
FROM AREA a
LEFT JOIN tmp2 t2_1 ON a.AREA_CODE = t2_1.code AND t2_1.year='2022'
LEFT JOIN tmp2 t2_2 ON a.AREA_CODE = t2_2.code AND t2_2.year='2017'
LEFT JOIN tmp2 t2_3 ON a.AREA_CODE = t2_3.code AND t2_3.year='2012'
ORDER BY t2_1.rank,a.AREA_CODE desc