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 
    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 
    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 
    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 
    left outer join T2017 on T2022.CODE = T2017.CODE
    left outer join T2012 on T2022.CODE = T2012.CODE
        
    order by 3 asc , 1 desc