with a as (
select
b.area_code as CODE
,b.area_name as NAME
,round((cast(food_exp as real) / cast(consumption_exp as real)) * 100,1) || '%' as "2022_RATIO"
from
household_survey a
inner join area b
on a.area_code = b.area_code
where a.survey_year = '2022'
)
,b as (
select
b.area_code as CODE
,b.area_name as NAME
,round((cast(food_exp as real) / cast(consumption_exp as real)) * 100,1) || '%' as "2017_RATIO"
from
household_survey a
inner join area b
on a.area_code = b.area_code
where a.survey_year = '2017'
)
,c as (
select
b.area_code as CODE
,b.area_name as NAME
,round((cast(food_exp as real) / cast(consumption_exp as real)) * 100,1) || '%' as "2012_RATIO"
from
household_survey a
inner join area b
on a.area_code = b.area_code
where a.survey_year = '2012'
)
select
a.CODE
,a.NAME
,a."2022_RATIO"
,DENSE_RANK() over(order by a."2022_RATIO") as "2022_RANK"
,b."2017_RATIO"
,DENSE_RANK() over(order by b."2017_RATIO") as "2017_RANK"
,c."2012_RATIO"
,DENSE_RANK() over(order by c."2012_RATIO") as "2012_RANK"
from a
inner join b
on a.CODE = b.CODE
and a.NAME = b.NAME
inner join c
on a.CODE = c.CODE
and a.NAME = c.NAME
order by
"2022_RANK"
,a.CODE desc