with R2022 as (
    select
        AREA_CODE,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) as RATIO,
        dense_rank() over(
            order by
                round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ) as RANK
    from
        HOUSEHOLD_SURVEY
    where
        SURVEY_YEAR == 2022
),
R2017 as (
    select
        AREA_CODE,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) as RATIO,
        dense_rank() over(
            order by
                round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ) as RANK
    from
        HOUSEHOLD_SURVEY
    where
        SURVEY_YEAR == 2017
),
R2012 as (
    select
        AREA_CODE,
        round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) as RATIO,
        dense_rank() over(
            order by
                round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1)
        ) as RANK
    from
        HOUSEHOLD_SURVEY
    where
        SURVEY_YEAR == 2012
)
select
    A.AREA_CODE as CODE,
    A.AREA_NAME as NAME,
    R2022.RANK as "2022_RANK",
    cast(R2022.RATIO as VARCHAR) || "%" as "2022_RATIO",
    R2017.RANK as "2017_RANK",
    cast(R2017.RATIO as VARCHAR) || "%" as "2017_RATIO",
    R2012.RANK as "2012_RANK",
    cast(R2012.RATIO as VARCHAR) || "%" as "2012_RATIO"
from
    AREA as A,
    R2022,
    R2017,
    R2012
where
    A.AREA_CODE == R2022.AREA_CODE
    and A.AREA_CODE == R2017.AREA_CODE
    and A.AREA_CODE == R2012.AREA_CODE
order by
    R2022.RANK,
    A.AREA_CODE desc