with T2022 as (
    select AREA_CODE as CODE
    , rank () over ( order by round ( 100.0 * ( cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real ) ) ,1 ) ) as 'RANK'
    , round ( 100.0 * ( cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real) ) ,1 )  || '%' as 'RATIO'
    from HOUSEHOLD_SURVEY as A
    where SURVEY_YEAR = '2022'
) 
, T2017 as (
    select AREA_CODE as CODE
    , rank () over ( order by round ( 100.0 * ( cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real ) ) ,1 ) ) as 'RANK'
    , round ( 100.0 * ( cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real) ) ,1 )  || '%' as 'RATIO'
    from HOUSEHOLD_SURVEY as A
    where SURVEY_YEAR = '2017'
) 
, T2012 as (
    select AREA_CODE as CODE
    , rank () over ( order by round ( 100.0 * ( cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real ) ) ,1 ) ) as 'RANK'
    , round ( 100.0 * ( cast(FOOD_EXP as real) / cast(CONSUMPTION_EXP as real) ) ,1 )  || '%' as 'RATIO'
    from HOUSEHOLD_SURVEY as A
    where SURVEY_YEAR = '2012'
) 

select 
    T2022.CODE as CODE
    , ( select AREA_NAME from AREA as B where T2022.CODE = B.AREA_CODE ) as NAME
    , T2022.RANK as '2022_RANK' , T2022.RATIO as '2022_RATIO'
    , T2017.RANK as '2017_RANK' , T2017.RATIO as '2017_RATIO'
    , T2012.RANK as '2012_RANK' , T2012.RATIO as '2012_RATIO'

    from T2022 inner join T2017 on T2022.CODE = T2017.CODE
    inner join T2012 on T2022.CODE = T2012.CODE

    order by '2022_RANK' , T2022.CODE