with tmp as(
select b.SURVEY_YEAR,a.AREA_CODE as CODE,
a.AREA_NAME as NAME,
round(
cast(b.FOOD_EXP as real)/
cast(b.CONSUMPTION_EXP as real) * 100,1) as RATIO,
dense_rank() over(partition by SURVEY_YEAR
order by cast(b.FOOD_EXP as real)/
cast(b.CONSUMPTION_EXP as real)) as rn
  from AREA a
  Join HOUSEHOLD_SURVEY b
    on a.AREA_CODE = b.AREA_CODE
  where b.SURVEY_YEAR in(2022,2017,2012))
select CODE,NAME,
max(case SURVEY_YEAR when 2022 then rn    end) as "2022_RANK",
max(case SURVEY_YEAR when 2022 then RATIO end) || '%' as "2022_RATIO",
max(case SURVEY_YEAR when 2017 then rn    end) as "2017_RANK",
max(case SURVEY_YEAR when 2017 then RATIO end) || '%' as "2017_RATIO",
max(case SURVEY_YEAR when 2012 then rn    end) as "2012_RANK",
max(case SURVEY_YEAR when 2012 then RATIO end) || '%' as "2012_RATIO"
  from tmp
group by CODE,NAME
order by "2022_RANK",CODE desc