with _2022 as 
(
    select
      a.AREA_CODE,
      a.AREA_NAME,
      DENSE_RANK() over (order by round(100.0*hs.FOOD_EXP/hs.CONSUMPTION_EXP,1)) `rank`,
      round(100.0*hs.FOOD_EXP/hs.CONSUMPTION_EXP,1) || "%" `ratio`
    from HOUSEHOLD_SURVEY hs
    left join AREA a using(AREA_CODE)
    where hs.SURVEY_YEAR = 2022
),
_2017 as 
(
    select
      a.AREA_CODE,
      DENSE_RANK() over (order by round(100.0*hs.FOOD_EXP/hs.CONSUMPTION_EXP,1)) `rank`,
      round(100.0*hs.FOOD_EXP/hs.CONSUMPTION_EXP,1) || "%" `ratio`
    from HOUSEHOLD_SURVEY hs
    left join AREA a using(AREA_CODE)
    where hs.SURVEY_YEAR = 2017
),
_2012 as 
(
    select
      a.AREA_CODE,
      DENSE_RANK() over (order by round(100.0*hs.FOOD_EXP/hs.CONSUMPTION_EXP,1)) `rank`,
      round(100.0*hs.FOOD_EXP/hs.CONSUMPTION_EXP,1) || "%" `ratio`
    from HOUSEHOLD_SURVEY hs
    left join AREA a using(AREA_CODE)
    where hs.SURVEY_YEAR = 2012
)
select
  y22.AREA_CODE CODE,
  y22.AREA_NAME NAME,  
  y22.rank `2022_RANK`,
  y22.ratio `2022_RATIO`,
  y17.rank `2017_RANK`,
  y17.ratio `2017_RATIO`,
  y12.rank `2012_RANK`,
  y12.ratio `2012_RATIO`
from _2022 y22
left join _2017 y17 using (AREA_CODE)
left join _2012 y12 using (AREA_CODE)
order by y22.rank, y22.AREA_CODE desc