with tmp2022 as (
    select
        h.area_code as CODE,
        area_name as NAME,
        dense_rank() over(order by round(cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real)*100,1)) as '2022_RANK',
        round(cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real)*100,1) || '%' as '2022_RATIO'
    from household_survey as h
    inner join area as a
    on h.area_code=a.area_code
    where survey_year=2022
),
tmp2017 as (
    select
        h.area_code as CODE,
        dense_rank() over(order by round(cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real)*100,1)) as '2017_RANK',
        round(cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real)*100,1) || '%' as '2017_RATIO'
    from household_survey as h
    where survey_year=2017
),
tmp2012 as (
    select
        h.area_code as CODE,
        dense_rank() over(order by round(cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real)*100,1)) as '2012_RANK',
        round(cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real)*100,1) || '%' as '2012_RATIO'
    from household_survey as h
    where survey_year=2012
)
select
    tmp2022.code as CODE,
    NAME,
    tmp2022.'2022_RANK',
    tmp2022.'2022_RATIO',
    tmp2017.'2017_RANK',
    tmp2017.'2017_RATIO',
    tmp2012.'2012_RANK',
    tmp2012.'2012_RATIO'
from tmp2022
inner join tmp2017
on tmp2022.code=tmp2017.code
inner join tmp2012
on tmp2022.code=tmp2012.code
order by tmp2022.'2022_RANK',code desc;