with tmp2022 as(
select AREA_CODE as CODE,
round(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
),1) as ratio22
from HOUSEHOLD_SURVEY
group by CODE
),
Rank2022 as(
select CODE,
DENSE_RANK() over(order by ratio22) as rank2022,
substr(cast(a.ratio22 as real) || '000', 1, length(cast(a.ratio22 as integer)) + 1 + 1) as ratio22
from tmp2022 a
),
tmp2017 as(
select AREA_CODE as CODE,
round(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
),1) as ratio17
from HOUSEHOLD_SURVEY
group by CODE
),
Rank2017 as(
select CODE,
DENSE_RANK() over(order by ratio17) as rank2017,
substr(cast(a.ratio17 as real) || '000', 1, length(cast(a.ratio17 as integer)) + 1 + 1) as ratio17
from tmp2017 a
),
tmp2012 as(
select AREA_CODE as CODE,
round(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
),1) as ratio12
from HOUSEHOLD_SURVEY
group by CODE
),
Rank2012 as(
select CODE,
DENSE_RANK() over(order by ratio12) as rank2012,
substr(cast(a.ratio12 as real) || '000', 1, length(cast(a.ratio12 as integer)) + 1 + 1) as ratio12
from tmp2012 a
)
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