with agg as(
select 
    h.AREA_CODE "CODE"
    ,a.AREA_NAME "NAME"
    ,SUM(CASE WHEN SURVEY_YEAR = 2022 THEN FOOD_EXP ELSE 0 END) "2022_FE_TTL"
    ,SUM(CASE WHEN SURVEY_YEAR = 2022 THEN CONSUMPTION_EXP ELSE 0 END) "2022_CE_TTL"
    ,SUM(CASE WHEN SURVEY_YEAR = 2017 THEN FOOD_EXP ELSE 0 END) "2017_FE_TTL"
    ,SUM(CASE WHEN SURVEY_YEAR = 2017 THEN CONSUMPTION_EXP ELSE 0 END) "2017_CE_TTL"
    ,SUM(CASE WHEN SURVEY_YEAR = 2012 THEN FOOD_EXP ELSE 0 END) "2012_FE_TTL"
    ,SUM(CASE WHEN SURVEY_YEAR = 2012 THEN CONSUMPTION_EXP ELSE 0 END) "2012_CE_TTL"
from HOUSEHOLD_SURVEY h
inner join AREA a
on h.AREA_CODE = a.AREA_CODE
group by h.AREA_CODE, a.AREA_NAME
)

select
    CODE
    ,NAME
    ,dense_rank() over(order by round(cast("2022_FE_TTL" as real) / "2022_CE_TTL" * 100,1) asc) "2022_RANK"
    ,round(cast("2022_FE_TTL" as real) / "2022_CE_TTL" * 100,1) || '%' "2022_RATIO"
    ,dense_rank() over(order by round(cast("2017_FE_TTL" as real) / "2017_CE_TTL" * 100,1) asc) "2017_RANK"
    ,round(cast("2017_FE_TTL" as real) / "2017_CE_TTL" * 100,1) || '%' "2017_RATIO"
    ,dense_rank() over(order by round(cast("2012_FE_TTL" as real) / "2012_CE_TTL" * 100,1) asc) "2012_RANK"
    ,round(cast("2012_FE_TTL" as real) / "2012_CE_TTL" * 100,1) || '%' "2012_RATIO"
from
    agg a
order by "2022_RANK", CODE desc
;