SELECT
SURVEY_2022.AREA_CODE AS CODE
,AREA.AREA_NAME AS NAME
,SURVEY_2022.[RANK] AS [2022_RANK]
,SURVEY_2022.[RATIO] || '%' AS [2022_RATIO]
,SURVEY_2017.[RANK] AS [2017_RANK]
,SURVEY_2017.[RATIO] || '%' AS [2017_RATIO]
,SURVEY_2012.[RANK] AS [2012_RANK]
,SURVEY_2012.[RATIO] || '%' AS [2012_RATIO]
FROM (
	SELECT
	AREA_CODE
	,DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP * 100 AS REAL) / CAST(CONSUMPTION_EXP AS REAL), 1)) AS [RANK]
	,ROUND(CAST(FOOD_EXP * 100 AS REAL) / CAST(CONSUMPTION_EXP AS REAL), 1) AS [RATIO]
	FROM HOUSEHOLD_SURVEY
	WHERE SURVEY_YEAR = 2022
) AS SURVEY_2022
INNER JOIN AREA
ON AREA.AREA_CODE = SURVEY_2022.AREA_CODE
LEFT OUTER JOIN (
	SELECT
	AREA_CODE
	,DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP * 100 AS REAL) / CAST(CONSUMPTION_EXP AS REAL), 1)) AS [RANK]
	,ROUND(CAST(FOOD_EXP * 100 AS REAL) / CAST(CONSUMPTION_EXP AS REAL), 1) AS [RATIO]
	FROM HOUSEHOLD_SURVEY
	WHERE SURVEY_YEAR = 2017
) AS SURVEY_2017
ON SURVEY_2017.AREA_CODE = SURVEY_2022.AREA_CODE
LEFT OUTER JOIN (
	SELECT
	AREA_CODE
	,DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP * 100 AS REAL) / CAST(CONSUMPTION_EXP AS REAL), 1)) AS [RANK]
	,ROUND(CAST(FOOD_EXP * 100 AS REAL) / CAST(CONSUMPTION_EXP AS REAL), 1) AS [RATIO]
	FROM HOUSEHOLD_SURVEY
	WHERE SURVEY_YEAR = 2012
) AS SURVEY_2012
ON SURVEY_2012.AREA_CODE = SURVEY_2022.AREA_CODE
ORDER BY
SURVEY_2022.[RANK] ASC
,SURVEY_2022.AREA_CODE DESC
;