with rank_2022 as(
select
    area_code 
    ,round(100.0*food_exp/consumption_exp,1) as ratio
    ,rank()over(order by round(100.0*food_exp/consumption_exp,1) ) as r
from household_survey
where survey_year = '2022'
)
,rank_2017 as(
select
    area_code 
    ,round(100.0*food_exp/consumption_exp,1) as ratio
    ,rank()over(order by round(100.0*food_exp/consumption_exp,1) ) as r
from household_survey
where survey_year = '2017'
)
,rank_2012 as(
select
    area_code 
    ,round(100.0*food_exp/consumption_exp,1) as ratio
    ,rank()over(order by round(100.0*food_exp/consumption_exp,1) ) as r
from household_survey
where survey_year = '2012'
)
select 
rank_2022.area_code as CODE
,area_name as NAME
,rank_2022.r as '2022_RANK'
,rank_2022.ratio||'%' as '2022_RATIO'
,rank_2017.r as '2017_RANK'
,rank_2017.ratio||'%' as '2017_RATIO'
,rank_2012.r as '2012_RANK'
,rank_2012.ratio||'%' as '2012_RATIO'
from rank_2022
inner join rank_2017
on rank_2022.area_code = rank_2017.area_code
inner join rank_2012
on rank_2022.area_code = rank_2012.area_code
inner join area
on area.area_code = rank_2022.area_code
order by rank_2022.r,CODE desc