select 
a.AREA_CODE as CODE,
a.AREA_NAME as NAME,
hs2022.RANK as '2022_RANK',
hs2022.RATIO || '%'  as '2022_RATIO',
hs2017.RANK as '2017_RANK',
hs2017.RATIO || '%'  as '2017_RATIO',
hs2012.RANK as '2012_RANK',
hs2012.RATIO || '%'  as '2012_RATIO'
from AREA a

join (
select 
AREA_CODE,
dense_rank() over(order by round( cast(FOOD_EXP as REAL)*100/cast(CONSUMPTION_EXP as REAL) , 1) asc) as 'RANK',
round( cast(FOOD_EXP as REAL)*100/cast(CONSUMPTION_EXP as REAL) , 1) as 'RATIO'
from HOUSEHOLD_SURVEY
where SURVEY_YEAR = 2022
) as hs2022
on a.AREA_CODE = hs2022.AREA_CODE

join (
select 
AREA_CODE,
dense_rank() over(order by round( cast(FOOD_EXP as REAL)*100/cast(CONSUMPTION_EXP as REAL) , 1) asc) as 'RANK',
round( cast(FOOD_EXP as REAL)*100/cast(CONSUMPTION_EXP as REAL) , 1) as 'RATIO'
from HOUSEHOLD_SURVEY
where SURVEY_YEAR = 2017
) as hs2017
on a.AREA_CODE = hs2017.AREA_CODE

join (
select 
AREA_CODE,
dense_rank() over(order by round( cast(FOOD_EXP as REAL)*100/cast(CONSUMPTION_EXP as REAL) , 1) asc) as 'RANK',
round( cast(FOOD_EXP as REAL)*100/cast(CONSUMPTION_EXP as REAL) , 1) as 'RATIO'
from HOUSEHOLD_SURVEY
where SURVEY_YEAR = 2012
) as hs2012
on a.AREA_CODE = hs2012.AREA_CODE

order by hs2022.RANK asc,CODE desc;