with r2022 as(
select s.area_code,round(cast(s.food_exp as real) /s.consumption_exp * 100, 1) rnk

from household_survey s
where s.survey_year = 2022
group by 1
),
r2017 as(
select s.area_code,round(cast(s.food_exp as real) /s.consumption_exp * 100, 1) rnk

from household_survey s
where s.survey_year = 2017
group by 1
),
r2012 as(
select s.area_code,round(cast(s.food_exp as real) /s.consumption_exp * 100, 1) rnk

from household_survey s
where s.survey_year = 2012
group by 1
)

select
    r2022.area_code CODE,
    area.area_name NAME,
    dense_rank() over(order by r2022.rnk) "2022_RANK",
    r2022.rnk || '%' "2022_RATIO",
    dense_rank() over(order by r2017.rnk)  "2017_RANK",
    r2017.rnk || '%' "2017_RATIO",
    dense_rank() over(order by r2012.rnk) "2012_RANK",
    r2012.rnk || '%' "2012_RATIO"
    

from r2022 
inner join r2017 using(area_code)
inner join r2012 using(area_code)
inner join area using(area_code)
order by
3,1 desc