with s2022 as (
select 
AREA_CODE
, dense_rank() over (order by (round(100.0 * food_exp / consumption_exp, 1))) as ra
, round(100.0 * food_exp / consumption_exp, 1) as ratio
from household_survey
where survey_year = 2022
)
, s2017 as (
select 
AREA_CODE
, dense_rank() over (order by (round(100.0 * food_exp / consumption_exp, 1))) as ra
, round(100.0 * food_exp / consumption_exp, 1) as ratio
from household_survey
where survey_year = 2017
)
, s2012 as (
select 
AREA_CODE
, dense_rank() over (order by (round(100.0 * food_exp / consumption_exp, 1))) as ra
, round(100.0 * food_exp / consumption_exp, 1) as ratio
from household_survey
where survey_year = 2012
)
select
s2022.AREA_CODE as CODE
, area.AREA_NAME as NAME
, s2022.ra as "2022_RANK"
, s2022.ratio || '%' as "2022_RATIO"
, s2017.ra as "2017_RANK"
, s2017.ratio || '%' as "2017_RATIO"
, s2012.ra as "2012_RANK"
, s2012.ratio || '%' as "2012_RATIO"
from s2022 join s2017 on s2022.AREA_CODE = s2017.AREA_CODE join s2012 on s2022.AREA_CODE = s2012.AREA_CODE join area on s2022.AREA_CODE = area.AREA_CODE
order by "2022_RANK", CODE desc