with rank_2017 as 
(
 select 
  s.area_code as CODE 
  ,area_name as NAME 
  ,row_number() over (order by round(100.0 * food_exp / consumption_exp, 1)) as '2017_RANK'
  ,round(100.0 * food_exp / consumption_exp, 1) || '%' as '2017_RATIO'
from household_survey as s 
inner join area as a 
  on s.area_code = a.area_code 
where survey_year = 2017
group by 1,2
)
,rank_2012 as 
(
 select 
  s.area_code as CODE 
  ,area_name as NAME 
  ,row_number() over (order by round(100.0 * food_exp / consumption_exp, 1)) as '2012_RANK'
  ,round(100.0 * food_exp / consumption_exp, 1) || '%' as '2012_RATIO'
from household_survey as s 
inner join area as a 
  on s.area_code = a.area_code 
where survey_year = 2012
group by 1,2
)
,rank_2022 as 
(
 select 
  s.area_code as CODE 
  ,area_name as NAME 
  ,row_number() over (order by round(100.0 * food_exp / consumption_exp, 1)) as '2022_RANK'
  ,round(100.0 * food_exp / consumption_exp, 1) || '%' as '2022_RATIO'
from household_survey as s 
inner join area as a 
  on s.area_code = a.area_code 
where survey_year = 2022
group by 1,2
)
select 
  CODE
  ,NAME 
  ,rank_2022.'2022_RANK' 
  ,rank_2022.'2022_RATIO'
  ,rank_2017.'2017_RANK'
  ,rank_2017.'2017_RATIO'
  ,rank_2012.'2012_RANK'
  ,rank_2012.'2012_RATIO'
from rank_2022
inner join rank_2017 using(code, name)
inner join rank_2012 using(code, name)

order by rank_2022.'2022_RANK' asc , CODE desc 
;