with survey_2022 as (
    select area_code, round(100.0 * food_exp / consumption_exp, 1) || '%' as `2022_RATIO`
    from household_survey
    where survey_year = 2022
    order by `2022_RATIO`
),
survey_2017 as (
    select area_code, round(100.0 * food_exp / consumption_exp, 1) || '%' as `2017_RATIO`
    from household_survey
    where survey_year = 2017
    order by `2017_RATIO`
),
survey_2012 as (
    select area_code, round(100.0 * food_exp / consumption_exp, 1) || '%' as `2012_RATIO`
    from household_survey
    where survey_year = 2012
    order by `2012_RATIO`
)
select
    area.area_code as CODE,
    area.area_name as NAME,
    dense_rank() over (order by `2022_RATIO`) as `2022_RANK`,
    `2022_RATIO`,
    dense_rank() over (order by `2017_RATIO`) as `2017_RANK`,
    `2017_RATIO`,
    dense_rank() over (order by `2012_RATIO`) as `2012_RANK`,
    `2012_RATIO`
from area
left join survey_2022
    on  area.area_code = survey_2022.area_code
left join survey_2017
    on area.area_code = survey_2017.area_code
left join survey_2012
    on area.area_code = survey_2012.area_code
order by
    `2022_RATIO` asc,
    CODE desc