with main_2022 as (
    select 
        area_code
        , round(cast(food_exp as real) / consumption_exp * 100, 1) as ratio
        , dense_rank() over (
            order by round(cast(food_exp as real) / consumption_exp * 100, 1) asc
        ) as rank
    from household_survey
    where survey_year=2022
),
sub_2017 as (
    select 
        area_code
        , round(cast(food_exp as real) / consumption_exp * 100, 1) as ratio
        , dense_rank() over (
            order by round(cast(food_exp as real) / consumption_exp * 100, 1) asc
        ) as rank
    from household_survey
    where survey_year=2017
),
sub_2012 as (
    select 
        area_code
        , round(cast(food_exp as real) / consumption_exp * 100, 1) as ratio
        , dense_rank() over (
            order by round(cast(food_exp as real) / consumption_exp * 100, 1) asc
        ) as rank
    from household_survey
    where survey_year=2012
)
select 
    m22.area_code as CODE
    , area_name as NAME
    , m22.rank as '2022_RANK'
    , m22.ratio || '%' as '2022_RATIO'
    , m22.rank as '2017_RANK'
    , m22.ratio || '%' as '2017_RATIO'
    , m22.rank as '2012_RANK'
    , m22.ratio || '%' as '2012_RATIO'
from main_2022 m22
inner join sub_2017 s17 on s17.area_code=m22.area_code
inner join sub_2012 s12 on s12.area_code=m22.area_code
inner join area on area.area_code=m22.area_code
order by m22.rank, m22.area_code desc;