with rank_2022 as (
    select
    AREA_CODE
    , dense_rank() over(order by round((cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real))*100,1) asc)  as RANK 
    , round((cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real))*100,1)                                  as RATIO
     from HOUSEHOLD_SURVEY
    where
        SURVEY_YEAR = 2022)
, rank_2017 as (
    select
    AREA_CODE
    , dense_rank() over(order by round((cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real))*100,1) asc)  as RANK 
    , round((cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real))*100,1)                                  as RATIO
     from HOUSEHOLD_SURVEY
    where
        SURVEY_YEAR = 2017)
, rank_2012 as (
    select
    AREA_CODE
    , dense_rank() over(order by round((cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real))*100,1) asc)  as RANK 
    , round((cast(FOOD_EXP as real)/cast(CONSUMPTION_EXP as real))*100,1)                                  as RATIO
     from HOUSEHOLD_SURVEY
    where
        SURVEY_YEAR = 2012)
select 
      R22.AREA_CODE  as CODE
    , AR.AREA_NAME   as NAME
    , R22.RANK       as '2022_RANK'
    , R22.RATIO||'%' as '2022_RATIO'
    , R17.RANK       as '2017_RANK'
    , R17.RATIO||'%' as '2017_RATIO'
    , R12.RANK       as '2012_RANK'
    , R12.RATIO||'%' as '2012_RATIO'
from  rank_2022 as R22
    inner join AREA as AR
        on AR.AREA_CODE = R22.AREA_CODE
    left join RANK_2017 as R17
        on R17.AREA_CODE = R22.AREA_CODE
    left join RANK_2012 as R12
        on R12.AREA_CODE = R22.AREA_CODE
order by R22.RANK asc
        ,CODE     desc