WITH rate_2022 AS (
SELECT
    SURVEY_YEAR
    , AREA_CODE
    , CONSUMPTION_EXP
    , FOOD_EXP 
    , round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1) as RANK
    , dense_rank() over (order by round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1)) as row
FROM
    HOUSEHOLD_SURVEY 
WHERE
    SURVEY_YEAR = '2022'
order by RANK
)

,rate_2021 AS (
SELECT
    SURVEY_YEAR
    , AREA_CODE
    , CONSUMPTION_EXP
    , FOOD_EXP 
    , round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1) as RANK
    , dense_rank() over (order by round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1)) as row
FROM
    HOUSEHOLD_SURVEY 
WHERE
    SURVEY_YEAR = '2021'
)

,rate_2017 AS (
SELECT
    SURVEY_YEAR
    , AREA_CODE
    , CONSUMPTION_EXP
    , FOOD_EXP 
    , round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1) as RANK
    , dense_rank() over (order by round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1)) as row
FROM
    HOUSEHOLD_SURVEY 
WHERE
    SURVEY_YEAR = '2017'
)
,rate_2012 AS (
SELECT
    SURVEY_YEAR
    , AREA_CODE
    , CONSUMPTION_EXP
    , FOOD_EXP 
    , round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1) as RANK
    , dense_rank() over (order by round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL) * 100 , 1)) as row
FROM
    HOUSEHOLD_SURVEY 
WHERE
    SURVEY_YEAR = '2012'
)

SELECT
    r2022.AREA_CODE as CODE
    , a.AREA_NAME as NAME
    , r2022.row as '2022_RANK'
    , r2022.RANK || "%" as '2022_RATIO'
    , r2017.row as '2017_RANK'
    , r2017.RANK || "%" as '2017_RATIO'
    , r2012.row as '2012_RANK'
    , r2012.RANK || "%" as '2012_RATIO' 
FROM
    rate_2022 r2022 
    inner join AREA a 
        on r2022.AREA_CODE = a.AREA_CODE 
    inner join rate_2017 r2017 
        on r2022.AREA_CODE = r2017.AREA_CODE 
    inner join rate_2012 r2012 
        on r2022.AREA_CODE = r2012.AREA_CODE 
order by
    r2022.row
    , CODE desc