with table_2012 AS(
    select a.area_code
        , round(cast(s.FOOD_EXP AS double) / cast(s.CONSUMPTION_EXP AS double) * 100, 1)
            || '%' AS '2012_RATIO'
        , DENSE_RANK() OVER(order by  round(cast(s.FOOD_EXP AS double) / cast(s.CONSUMPTION_EXP AS double) * 100, 1) asc)
            AS '2012_RANK'
    from AREA a
    inner join HOUSEHOLD_SURVEY s
        on a.area_code = s.area_code
    where s.survey_year = 2012
    group by a.area_code
)
, table_2017 AS(
    select a.area_code
        , round(cast(s.FOOD_EXP AS double) / cast(s.CONSUMPTION_EXP AS double) * 100, 1)
            || '%' AS '2017_RATIO'
        , DENSE_RANK() OVER(order by  round(cast(s.FOOD_EXP AS double) / cast(s.CONSUMPTION_EXP AS double) * 100, 1) asc)
            AS '2017_RANK'
    from AREA a
    inner join HOUSEHOLD_SURVEY s
        on a.area_code = s.area_code
    where s.survey_year = 2017
    group by a.area_code
)
, table_2022 AS(
    select a.area_code
        , round(cast(s.FOOD_EXP AS double) / cast(s.CONSUMPTION_EXP AS double) * 100, 1)
            || '%' AS '2022_RATIO'
        , DENSE_RANK() OVER(order by  round(cast(s.FOOD_EXP AS double) / cast(s.CONSUMPTION_EXP AS double) * 100, 1) asc)
            AS '2022_RANK'
    from AREA a
    inner join HOUSEHOLD_SURVEY s
        on a.area_code = s.area_code
    where s.survey_year = 2022
    group by a.area_code
)
select
    a.area_code AS CODE
    , a.area_name AS NAME
    , t2022.'2022_RANK'
    , t2022.'2022_RATIO'
    , t2017.'2017_RANK'
    , t2017.'2017_RATIO'
    , t2012.'2012_RANK'
    , t2012.'2012_RATIO'
from area a
inner join table_2012 t2012
    on a.area_code = t2012.area_code
inner join table_2017 t2017
    on a.area_code = t2017.area_code
inner join table_2022 t2022
    on a.area_code = t2022.area_code
order by t2022.'2022_RANK', a.area_code desc;