with tmp2022 as(
select AREA_CODE as CODE,
100.0*sum(
case 
when SURVEY_YEAR=2022 then FOOD_EXP
else 0
end
)/sum(
case 
when SURVEY_YEAR=2022 then CONSUMPTION_EXP
else 0
end
) as ratio22
--,ROW_NUMBER() over(order by ratio) as rank2022
from HOUSEHOLD_SURVEY
group by CODE
order by ratio22 
),
rank2022 as(
select CODE,
ROW_NUMBER() over() as rank2022,
ratio22
from tmp2022
),
tmp2017 as(
select AREA_CODE as CODE,
100.0*sum(
case 
when SURVEY_YEAR=2017 then FOOD_EXP
else 0
end
)/sum(
case 
when SURVEY_YEAR=2017 then CONSUMPTION_EXP
else 0
end
) as ratio17
from HOUSEHOLD_SURVEY
group by CODE
order by ratio17 
),
rank2017 as(
select CODE,
ROW_NUMBER() over() as rank2017,
ratio17
from tmp2017
),
tmp2012 as(
select AREA_CODE as CODE,
100.0*sum(
case 
when SURVEY_YEAR=2012 then FOOD_EXP
else 0
end
)/sum(
case 
when SURVEY_YEAR=2012 then CONSUMPTION_EXP
else 0
end
) as ratio12
--,ROW_NUMBER() over(order by ratio) as rank2022
from HOUSEHOLD_SURVEY
group by CODE
order by ratio12 
),
rank2012 as(
select CODE,
ROW_NUMBER() over() as rank2012,
ratio12
from tmp2012
)
select a.CODE as CODE, 
n.AREA_NAME as NAME, 
a.rank2022 as '2022_RANK', 
substr(cast(a.ratio22 as real) || '000', 1, length(cast(a.ratio22 as integer)) + 1 + 1)||'%' as '2022_RATIO',
b.rank2017 as '2017_RANK', 
substr(cast(b.ratio17 as real) || '000', 1, length(cast(b.ratio17 as integer)) + 1 + 1)||'%' as '2017_RATIO',
c.rank2012 as '2012_RANK', 
substr(cast(c.ratio12 as real) || '000', 1, length(cast(c.ratio12 as integer)) + 1 + 1)||'%' as '2012_RATIO'
from rank2022 a 
left join AREA n on n.AREA_CODE = a.CODE
left join rank2017 b on a.CODE = b.CODE
left join rank2012 c on a.CODE = c.CODE
order by 3,1 desc