with subq as(
select survey_year
	,area_code
	,sum(cast(food_exp as float))
	,sum(cast(consumption_exp as float))
	,round(sum(cast(food_exp as float)) / sum(cast(consumption_exp as float)),3) as rate_bef
	,cast(round(sum(cast(food_exp as float)) / sum(cast(consumption_exp as float)),3)*100 as varchar) || '%' as ratio
from household_survey
group by survey_year
        ,area_code
)

,subq2 as(
select *,dense_rank() over (partition by survey_year order by rate_bef) as rank
from subq
)

select subq2.area_code as CODE
        ,area.area_name as NAME
        ,subq2.rank as [2022_RANK]
        ,subq2.ratio as [2022_RATIO]
        ,subq_2017.rank as [2017_RANK]
        ,subq_2017.ratio as [2017_RATIO]
        ,subq_2012.rank as [2012_RANK]
        ,subq_2012.ratio as [2012_RATIO]
        
        
        
from subq2 
left outer join area 
on subq2.area_code = area.area_code
left outer join subq2 as subq_2017
on subq2.area_code = subq_2017.area_code
and subq_2017.survey_year = 2017

left outer join subq2 as subq_2012
on subq2.area_code = subq_2012.area_code
and subq_2012.survey_year = 2012
where subq2.survey_year = 2022
order by subq2.rank,subq2.area_code desc