with ranking_2022 as(
    select
        a.AREA_CODE as CODE,
        a.AREA_NAME as NAME,
        DENSE_rank() over(order by round((100*cast(s.FOOD_EXP as real) / cast(s.CONSUMPTION_EXP as real)),1)) as RANK_2022,
        round((100*cast(s.FOOD_EXP as real) / cast(s.CONSUMPTION_EXP as real)),1) as RAITO_2022
    from
        HOUSEHOLD_SURVEY as s
    inner join
        AREA as a on s.AREA_CODE  = a.AREA_CODE
    where
        SURVEY_YEAR = 2022
), ranking_2017 as(
    select
        a.AREA_CODE as CODE,
        a.AREA_NAME as NAME,
        DENSE_rank() over(order by round((100*cast(s.FOOD_EXP as real) / cast(s.CONSUMPTION_EXP as real)),1)) as RANK_2017,
        round((100*cast(s.FOOD_EXP as real) / cast(s.CONSUMPTION_EXP as real)),1) as RAITO_2017
    from
        HOUSEHOLD_SURVEY as s
    inner join
        AREA as a on s.AREA_CODE  = a.AREA_CODE
    where
        SURVEY_YEAR = 2017
), ranking_2012 as(
    select
        a.AREA_CODE as CODE,
        a.AREA_NAME as NAME,
        DENSE_rank() over(order by round((100*cast(s.FOOD_EXP as real) / cast(s.CONSUMPTION_EXP as real)),1)) as RANK_2012,
        round((100*cast(s.FOOD_EXP as real) / cast(s.CONSUMPTION_EXP as real)),1) as RAITO_2012
    from
        HOUSEHOLD_SURVEY as s
    inner join
        AREA as a on s.AREA_CODE  = a.AREA_CODE
    where
        SURVEY_YEAR = 2012
)
select
    ranking_2022.code as CODE,
    ranking_2022.NAME as NAME,
    ranking_2022.RANK_2022 as "2022_RANK",
    ranking_2022.RAITO_2022 || '%' as "2022_RAITO",
    ranking_2017.RANK_2017 as "2017_RANK",
    ranking_2017.RAITO_2017 || '%' as "2017_RAITO",
    ranking_2012.RANK_2012 as "2012_RANK",
    ranking_2012.RAITO_2012 || '%' as "2012_RAITO"
from
    ranking_2022
inner join
    ranking_2017 on ranking_2017.CODE = ranking_2022.CODE
inner join
    ranking_2012 on ranking_2012.CODE = ranking_2022.CODE
order by
    "2022_RANK",
    CODE desc
limit 5
;