with tbl1 as (
    select *
        ,round(cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real) * 100,1) || '%' as RATIO
    from HOUSEHOLD_SURVEY
    left join AREA
    on HOUSEHOLD_SURVEY.AREA_CODE = AREA.AREA_CODE
)
, tbl2022 as (
    select *
        ,dense_rank () OVER (ORDER BY RATIO) as RANK
    from tbl1
    where SURVEY_YEAR = 2022
)
, tbl2017 as (
    select *
        ,dense_rank () OVER (ORDER BY RATIO) as RANK
    from tbl1
    where SURVEY_YEAR = 2017
)
, tbl2012 as (
    select *
        ,dense_rank () OVER (ORDER BY RATIO) as RANK
    from tbl1
    where SURVEY_YEAR = 2012
)
select 
    tbl2022.AREA_CODE as CODE
    ,tbl2022.AREA_NAME as NAME
    ,tbl2022.RANK as "2022_RANK"
    ,tbl2022.RATIO as "2022_RATIO"
    ,tbl2017.RANK as "2017_RANK"
    ,tbl2017.RATIO as "2017_RATIO"
    ,tbl2012.RANK as "2012_RANK"
    ,tbl2012.RATIO as "2012_RATIO"
from tbl2022
    left join tbl2017
    on tbl2022.AREA_CODE = tbl2017.AREA_CODE
    left join tbl2012
    on tbl2022.AREA_CODE = tbl2012.AREA_CODE
order by 
    "2022_RANK"
    ,tbl2022.AREA_CODE desc