WITH A AS (
	SELECT
		AREA_CODE
		,ROUND((FOOD_EXP*1.00/CONSUMPTION_EXP*1.00)*100,1) RATIO
		,DENSE_RANK() OVER (ORDER BY ROUND((FOOD_EXP*1.00/CONSUMPTION_EXP*1.00)*100,1)) RANK
	FROM
		HOUSEHOLD_SURVEY
	WHERE
		SURVEY_YEAR = 2022
)
, B AS (
	SELECT
		AREA_CODE
		,ROUND((FOOD_EXP*1.00/CONSUMPTION_EXP*1.00)*100,1) RATIO
		,DENSE_RANK() OVER (ORDER BY ROUND((FOOD_EXP*1.00/CONSUMPTION_EXP*1.00)*100,1)) RANK
	FROM
		HOUSEHOLD_SURVEY
	WHERE
		SURVEY_YEAR = 2017
)
, C AS (
	SELECT
		AREA_CODE
		,ROUND((FOOD_EXP*1.00/CONSUMPTION_EXP*1.00)*100,1) RATIO
		,DENSE_RANK() OVER (ORDER BY ROUND((FOOD_EXP*1.00/CONSUMPTION_EXP*1.00)*100,1)) RANK
	FROM
		HOUSEHOLD_SURVEY
	WHERE
		SURVEY_YEAR = 2012
)
SELECT
	A.AREA_CODE AS CODE
	,D.AREA_NAME AS NAME
	,A.RANK AS '2022_RANK'
	,A.RATIO || '%'  AS '2022_RATIO'
	,B.RANK AS '2017_RANK'
	,B.RATIO || '%'  AS '2017_RATIO'
	,C.RANK AS '2012_RANK'
	,C.RATIO || '%' AS '2012_RATIO'
FROM A,B,C,AREA D
WHERE
	A.AREA_CODE = B.AREA_CODE
AND	A.AREA_CODE = C.AREA_CODE 
AND A.AREA_CODE = D.AREA_CODE
ORDER BY
	A.RANK,A.AREA_CODE DESC
;