with tmp as (
select
    a.area_code CODE
    ,a.area_name NAME
    ,max(case
        when hs.survey_year = 2022 then round((hs.food_exp * 1.0 / hs.consumption_exp) * 100, 1) 
        else 0
    end) as "2022_RATIO"
    ,max(case
        when hs.survey_year = 2017 then round((hs.food_exp * 1.0 / hs.consumption_exp) * 100, 1) 
        else 0
    end) as "2017_RATIO"
    ,max(case
        when hs.survey_year = 2012 then round((hs.food_exp * 1.0 / hs.consumption_exp) * 100, 1) 
        else 0
    end) as "2012_RATIO"
from
    household_survey hs left join area a on hs.area_code = a.area_code
group by
    a.area_code, a.area_name
order by
    "2022_RATIO", CODE desc
)
select
    code
    ,name
    ,dense_rank() over(order by "2022_RATIO") "2022_RANK"
    ,cast("2022_RATIO" as float) || '%' as "2022_RATIO"
    ,dense_rank() over(order by "2017_RATIO") "2017_RANK"
    ,cast("2017_RATIO" as float) || '%' as "2017_RATIO"
    ,dense_rank() over(order by "2012_RATIO") "2012_RANK"
    ,cast("2012_RATIO" as float) || '%' as "2012_RATIO"
from
    tmp
order by
    "2022_RATIO", CODE desc
;