with TBL_2022 as ( 
    select
        AREA_CODE
        , ROUND( 
            CAST(FOOD_EXP as real) / CONSUMPTION_EXP * 100
            , 1
        ) as RATIO
        , DENSE_RANK() OVER ( 
            ORDER BY
                ROUND( 
                    CAST(FOOD_EXP as real) / CONSUMPTION_EXP * 100
                    , 1
                ) asc
        ) as RANK 
    from
        HOUSEHOLD_SURVEY 
    where
        SURVEY_YEAR = 2022
) 
, TBL_2017 as ( 
    select
        AREA_CODE
        , ROUND( 
            CAST(FOOD_EXP as real) / CONSUMPTION_EXP * 100
            , 1
        ) as RATIO
        , DENSE_RANK() OVER ( 
            ORDER BY
                ROUND( 
                    CAST(FOOD_EXP as real) / CONSUMPTION_EXP * 100
                    , 1
                ) asc
        ) as RANK 
    from
        HOUSEHOLD_SURVEY 
    where
        SURVEY_YEAR = 2017
) 
, TBL_2012 as ( 
    select
        AREA_CODE
        , ROUND( 
            CAST(FOOD_EXP as real) / CONSUMPTION_EXP * 100
            , 1
        ) as RATIO
        , DENSE_RANK() OVER ( 
            ORDER BY
                ROUND( 
                    CAST(FOOD_EXP as real) / CONSUMPTION_EXP * 100
                    , 1
                ) asc
        ) as RANK 
    from
        HOUSEHOLD_SURVEY 
    where
        SURVEY_YEAR = 2012
) 
select
    T22.AREA_CODE as CODE,
AREA.AREA_NAME as NAME,
T22.RANK as "2022_RANK",
T22.RATIO || '%' as "2022_RATIO",
T17.RANK as "2017_RANK",
T22.RATIO || '%' as "2017_RATIO",
T17.RANK as "2012_RANK",
T22.RATIO || '%' as "2012_RATIO"
from
    TBL_2022 as T22
    inner join TBL_2017 as T17 
        on T22.AREA_CODE = T17.AREA_CODE 
    inner join TBL_2012 as T12 
        on T22.AREA_CODE = T12.AREA_CODE
    inner join AREA on T22.AREA_CODE = AREA.AREA_CODE 
order by "2022_RANK" asc, AREA.AREA_CODE desc