with
    t as (
        select
            area_code
            ,survey_year
            ,round((cast(food_exp as real) / cast(consumption_exp as real) * 100), 1) as ratio
        from
            household_survey
        where
            survey_year in (2022, 2017, 2012)
    )
    ,t2022 as (
        select
            area_code
            ,dense_rank() over (order by ratio asc) as rank
            ,ratio
        from
            t
        where
            survey_year = 2022
    )
    ,t2017 as (
        select
            area_code
            ,dense_rank() over (order by ratio asc) as rank
            ,ratio
        from
            t
        where
            survey_year = 2017
    )
    ,t2012 as (
        select
            area_code
            ,dense_rank() over (order by ratio asc) as rank
            ,ratio
        from
            t
        where
            survey_year = 2012
    )

select
    t2022.area_code as CODE
    ,area.area_name as NAME
    ,t2022.rank as "2022_RANK"
    ,t2022.ratio || '%' as "2022_RATIO"
    ,t2017.rank as "2017_RANK"
    ,t2017.ratio || '%' as "2017_RATIO"
    ,t2012.rank as "2012_RANK"
    ,t2012.ratio || '%' as "2012_RATIO"
from
    t2022
join
    t2017
on
    t2022.area_code = t2017.area_code
join
    t2012
on
    t2022.area_code = t2012.area_code
join
    area
on
    t2022.area_code = area.area_code
order by
    3 asc
    ,1 desc