with s2022 as(
select
	 AREA_CODE
	,round(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0 , 1) || '%' as RATIO
	,rank() over (order by round(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0 , 1)) as RANK
from HOUSEHOLD_SURVEY
where SURVEY_YEAR = 2022
)
, s2017 as(
select
	 AREA_CODE
	,round(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0 , 1) || '%' as RATIO
	,rank() over (order by round(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0 , 1)) as RANK
from HOUSEHOLD_SURVEY
where SURVEY_YEAR = 2017
)
, s2012 as(
select
	 AREA_CODE
	,round(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0 , 1) || '%' as RATIO
	,rank() over (order by round(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0 , 1)) as RANK
from HOUSEHOLD_SURVEY
where SURVEY_YEAR = 2012
)
select
	 s2022.AREA_CODE as CODE
	,AREA.AREA_NAME  as NAME
	,s2022.RANK as "2022_RANK"
	,s2022.RATIO as "2022_RATIO"
	,s2017.RANK as "2017_RANK"
	,s2017.RATIO as "2017_RATIO"
	,s2012.RANK as "2012_RANK"
	,s2012.RATIO as "2012_RATIO"
from s2022
	 inner join s2017
	 on  s2022.AREA_CODE = s2017.AREA_CODE
	 inner join s2012
	 on  s2017.AREA_CODE = s2012.AREA_CODE
	 inner join AREA
	 on  s2022.AREA_CODE = AREA.AREA_CODE
order by
	 s2022.RANK
	,s2022.AREA_CODE desc