select
  t."CODE" as "CODE"
 ,t."NAME" as "NAME"
 ,dense_rank() over (order by t."2022_RATIO" asc) as "2022_RANK"
 ,t."2022_RATIO" || '%' as "2022_RATIO"
 ,dense_rank() over (order by t."2017_RATIO" asc) as "2017_RANK"
 ,t."2017_RATIO" || '%' as "2017_RATIO"
 ,dense_rank() over (order by t."2012_RATIO" asc) as "2012_RANK"
 ,t."2012_RATIO" || '%' as "2012_RATIO"
from
 (
  select
    a.AREA_CODE as "CODE"
   ,a.AREA_NAME as "NAME"
   ,SUM(case hy.SURVEY_YEAR when 2022 then round(cast(hy.FOOD_EXP as real) / cast(hy.CONSUMPTION_EXP as real) * 100, 1) end) as "2022_RATIO"
   ,SUM(case hy.SURVEY_YEAR when 2017 then round(cast(hy.FOOD_EXP as real) / cast(hy.CONSUMPTION_EXP as real) * 100, 1) end) as "2017_RATIO"
   ,SUM(case hy.SURVEY_YEAR when 2012 then round(cast(hy.FOOD_EXP as real) / cast(hy.CONSUMPTION_EXP as real) * 100, 1) end) as "2012_RATIO"
  from
   AREA a
    join
   HOUSEHOLD_SURVEY hy
    on
   a.AREA_CODE = hy.AREA_CODE
  group by
   "CODE"
 ) t
group by
 "CODE"
order by
 "2022_RANK" asc, "CODE" desc
;