with t1 as(
select
    h.SURVEY_YEAR as year
    ,a.area_code as CODE
    ,a.area_name as NAME
    ,round(food_exp*1.0/CONSUMPTION_EXP*100,1)||'%'as ratio

    
from
    HOUSEHOLD_SURVEY as h
    left join
     AREA as a
     on h.area_code=a.area_code
where
    SURVEY_YEAR in (2022,2017,2012)
    )
    ,t2 as(
    select
        *
        ,DENSE_RANK()over(partition by year order by ratio)as rnk
    from
        t1
    )
,T3 AS(
    select
        code as CODE
        ,name as NAME
        ,MAX(case when year=2022 then rnk end) as '2022_RANK'
        ,MAX(case when year=2022 then ratio end) as '2022_RATIO'
        ,MAX(case when year=2017 then rnk end) as '2017_RANK'
        ,MAX(case when year=2017 then RATIO end) as '2017_RATIO'
        ,MAX(case when year=2012 then rnk end) as '2012_RANK'
        ,MAX(case when year=2012 then RATIO end) as '2012_RATIO'
    from
        t2
    GROUP BY
        CODE,NAME
)
SELECT
    *
FROM
    T3
ORDER BY 3 ASC, 1 DESC