with hs22 as(
    select
        aa.AREA_CODE as CODE,
        aa.AREA_NAME as NAME,
        dense_rank() over (ORDER BY round((cast(hs.FOOD_EXP as real)/cast(hs.CONSUMPTION_EXP as real)) * 100,1) ASC) as rank,
        round((cast(hs.FOOD_EXP as real)/cast(hs.CONSUMPTION_EXP as real)) * 100,1) || '%' as ratio
    from HOUSEHOLD_SURVEY as hs
    inner join AREA as aa on hs.AREA_CODE = aa.AREA_CODE
    where SURVEY_YEAR = 2022
),hs17 as (
    select
        aa.AREA_CODE as CODE,
        aa.AREA_NAME as NAME,
        dense_rank() over (ORDER BY round((cast(hs.FOOD_EXP as real)/cast(hs.CONSUMPTION_EXP as real)) * 100,1) ASC) as rank,
        round((cast(hs.FOOD_EXP as real)/cast(hs.CONSUMPTION_EXP as real)) * 100,1) || '%' as ratio
    from HOUSEHOLD_SURVEY as hs
    inner join AREA as aa on hs.AREA_CODE = aa.AREA_CODE
    where SURVEY_YEAR = 2017
),hs12 as (
    select
        aa.AREA_CODE as CODE,
        aa.AREA_NAME as NAME,
        dense_rank() over (ORDER BY round((cast(hs.FOOD_EXP as real)/cast(hs.CONSUMPTION_EXP as real)) * 100,1) ASC) as rank,
        round((cast(hs.FOOD_EXP as real)/cast(hs.CONSUMPTION_EXP as real)) * 100,1) || '%' as ratio
    from HOUSEHOLD_SURVEY as hs
    inner join AREA as aa on hs.AREA_CODE = aa.AREA_CODE
    where SURVEY_YEAR = 2012
)
select 
    aa.AREA_CODE as CODE,
    aa.AREA_NAME as NAME,
    hs22.rank as '2022_RANK',
    hs22.ratio as '2022_RATIO',
    hs17.rank as '2017_RANK',
    hs17.ratio as '2017_RATIO',
    hs12.rank as '2012_RANK',
    hs12.ratio as '2012_RATIO'
from AREA as aa
inner join hs22 on aa.AREA_CODE = hs22.CODE
inner join hs17 on aa.AREA_CODE = hs17.CODE
inner join hs12 on aa.AREA_CODE = hs12.CODE
order by
    hs22.rank asc,
    CODE desc;