SELECT
SURVEY_2022.AREA_CODE AS CODE
,AREA.AREA_NAME AS NAME
,RANK() OVER(ORDER BY ROUND(CAST(SURVEY_2022.FOOD_EXP * 100 AS REAL) / CAST(SURVEY_2022.CONSUMPTION_EXP AS REAL), 1) ASC) AS [2022_RANK]
,CAST(ROUND(CAST(SURVEY_2022.FOOD_EXP * 100 AS REAL) / CAST(SURVEY_2022.CONSUMPTION_EXP AS REAL), 1) AS TEXT) || '%' AS [2022_RATIO]

,RANK() OVER(ORDER BY ROUND(CAST(SURVEY_2017.FOOD_EXP * 100 AS REAL) / CAST(SURVEY_2017.CONSUMPTION_EXP AS REAL), 1) ASC) AS [2017_RANK]
,CAST(ROUND(CAST(SURVEY_2017.FOOD_EXP * 100 AS REAL) / CAST(SURVEY_2017.CONSUMPTION_EXP AS REAL), 1) AS TEXT) || '%' AS [2017_RATIO]

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