with result_2022 AS
(
    select 
         area_code
        ,ROUND(100.0 * food_exp / consumption_exp, 1) r
        ,row_number() over (order by ROUND(100.0 * food_exp / consumption_exp, 1 ) ) ra
    from 
        household_survey
    where survey_year = 2022
    group by area_code
), 
result_2017 AS
(
    select 
         area_code
        ,ROUND(100.0 * food_exp / consumption_exp, 1) r 
        ,row_number() over (order by ROUND(100.0 * food_exp / consumption_exp, 1 ) ) ra 
    from
        household_survey
    where survey_year = 2017
    group by area_code
), 
result_2012 AS
(
    select 
         area_code
        ,ROUND(100.0 * food_exp / consumption_exp, 1) r 
        ,row_number() over (order by ROUND(100.0 * food_exp / consumption_exp, 1 ) ) ra
    from
        household_survey
    where survey_year = 2012
    group by area_code
)
select 
     result_2022.area_code code
    ,area.area_name NAME
    ,result_2022.ra "2022_RANK"
    ,result_2022.r || '%'  "2022_RATIO"
    ,result_2017.ra "2017_RANK"
    ,result_2017.r || '%'  "2017_RATIO"  
    ,result_2012.ra "2012_RANK"
    ,result_2012.r || '%'  "2012_RATIO"
from
    result_2022
inner join
    result_2017
on
    result_2022.area_code = result_2017.area_code
inner join  
    result_2012
on
    result_2022.area_code = result_2012.area_code
inner join
    area
on
    result_2022.area_code = area.area_code
order by "2022_RANK", code desc

;