with ratio2022 as (
select 
h.AREA_CODE as CODE,
a.AREA_NAME as NAME,
round(cast(sum(FOOD_EXP) as real) / sum(CONSUMPTION_EXP) * 100, 1)  as RATIO
from   HOUSEHOLD_SURVEY h
join   AREA a on h.AREA_CODE = a.AREA_CODE
where  h.SURVEY_YEAR = 2022
group by h.AREA_CODE,a.AREA_NAME
), ratio2017 as (
select 
h.AREA_CODE as CODE,
a.AREA_NAME as NAME,
round(cast(sum(FOOD_EXP) as real) / sum(CONSUMPTION_EXP) * 100, 1)  as RATIO
from   HOUSEHOLD_SURVEY h
join   AREA a on h.AREA_CODE = a.AREA_CODE
where  h.SURVEY_YEAR = 2017
group by h.AREA_CODE,a.AREA_NAME
), ratio2012 as (
select 
h.AREA_CODE as CODE,
a.AREA_NAME as NAME,
cast(round(cast(sum(FOOD_EXP) as real) / sum(CONSUMPTION_EXP) * 100, 1) as real) as RATIO
from   HOUSEHOLD_SURVEY h
join   AREA a on h.AREA_CODE = a.AREA_CODE
where  h.SURVEY_YEAR = 2012
group by h.AREA_CODE,a.AREA_NAME
)
select ratio2022.CODE, ratio2022.NAME,
DENSE_RANK()over(ORDER BY ratio2022.RATIO) as "2022_RANK",
ratio2022.RATIO || '%' as "2022_RATIO",
DENSE_RANK()over(ORDER BY ratio2017.RATIO) as "2017_RANK",
ratio2017.RATIO || '%' as "2017_RATIO",
DENSE_RANK()over(ORDER BY ratio2012.RATIO) as "2012_RANK",
ratio2012.RATIO || '%' as "2012_RATIO"
from   ratio2022
left join ratio2017
on  ratio2022.CODE = ratio2017.CODE
left join ratio2012
on  ratio2022.CODE = ratio2012.CODE
order by 3, 1 desc