with target as (
    select
        h.AREA_CODE as 'CODE'
        , AREA_NAME as 'NAME'
        , SURVEY_YEAR
        , round((cast(FOOD_EXP as REAL)/ cast(CONSUMPTION_EXP as REAL) * 100.0), 1) as 'RATIO'
    from
        HOUSEHOLD_SURVEY h
        inner join AREA a on h.AREA_CODE = a.AREA_CODE
    where
        SURVEY_YEAR in ('2022', '2017', '2012')
    group by
        SURVEY_YEAR
        , h.AREA_CODE
), 2022_rank as (
    select
        CODE
        , NAME
        , dense_rank() over (order by RATIO asc) as '2022_RANK'
        , RATIO || '%' as '2022_RATIO'
    from
        target
    where
        SURVEY_YEAR = '2022'
), 2017_rank as (
    select
        CODE
        , NAME
        , dense_rank() over (order by RATIO asc) as '2017_RANK'
        , RATIO || '%' as '2017_RATIO'
    from
        target
    where
        SURVEY_YEAR = '2017'
), 2012_rank as (
    select
        CODE
        , NAME
        , dense_rank() over (order by RATIO asc) as '2012_RANK'
        , RATIO || '%' as '2012_RATIO'
    from
        target
    where
        SURVEY_YEAR = '2012'
)
select
    *
from
    2022_rank
    inner join 2017_rank on 2022_rank.CODE = 2017_rank.CODE
    inner join 2012_rank on 2022_rank.CODE = 2012_rank.CODE
;