with T2022 as (
    select AREA_CODE as CODE
    , dense_rank () over ( order by round ( FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0  ,1 ) ) as RANK
    , round ( FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0  ,1 )  || '%' as RATIO
    from HOUSEHOLD_SURVEY 
    where SURVEY_YEAR = '2022'
) 
, T2017 as (
    select AREA_CODE as CODE
    , dense_rank () over ( order by round ( FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0  ,1 ) ) as RANK
    , round ( FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0  ,1 )  || '%' as RATIO
    from HOUSEHOLD_SURVEY 
    where SURVEY_YEAR = '2017'
) 
, T2012 as (
    select AREA_CODE as CODE
    , dense_rank () over ( order by round ( FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0  ,1 ) ) as RANK
    , round ( FOOD_EXP * 1.0 / CONSUMPTION_EXP * 100.0  ,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
    inner join T2017 on T2022.CODE = T2017.CODE
    inner join T2012 on T2022.CODE = T2012.CODE
        
    order by 3 asc , 1 desc