WITH base AS(
	SELECT
		AREA_CODE AS CODE
		,SURVEY_YEAR
		,ROUND(CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL) * 100,1) AS RATIO
	FROM HOUSEHOLD_SURVEY
	WHERE SURVEY_YEAR IN(2012,2017,2022)
)
,rankdata AS (
	SELECT
		CODE
		,SURVEY_YEAR
		,DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY RATIO) AS Ratio_Rank
		,RATIO
	FROM base
)
SELECT
	t0.CODE
	,AREA_NAME AS NAME
	,t0.Ratio_Rank AS '2022_RANK'
	,t0.RATIO || '%'AS '2022_RATIO'
	,t2017.Ratio_Rank AS '2017_RANK'
	,t2017.RATIO || '%'AS '2017_RATIO'
	,t2012.Ratio_Rank AS '2012_RANK'
	,t2012.RATIO || '%'AS '2012_RATIO'
FROM rankdata AS t0
INNER JOIN AREA
	ON t0.CODE = AREA_CODE
INNER JOIN rankdata AS t2017
	ON t0.CODE = t2017.CODE
		AND t2017.SURVEY_YEAR = 2017
INNER JOIN rankdata AS t2012
	ON t0.CODE = t2012.CODE
		AND t2012.SURVEY_YEAR = 2012
WHERE t0.SURVEY_YEAR = 2022
ORDER BY t0.Ratio_Rank ASC,t0.CODE DESC