with y2022 as(
SELECT
area_code,
round((FOOD_EXP*100.0 / h.CONSUMPTION_EXP),1) a


FROM
HOUSEHOLD_SURVEY h
where survey_year=2022

group by 
Area_code
),
 y2017 as(
SELECT
area_code,
round((FOOD_EXP*100.0 / h.CONSUMPTION_EXP),1) b

FROM
HOUSEHOLD_SURVEY h
where survey_year=2017

group by 
Area_code
),
 y2012 as(
SELECT
area_code,
round((FOOD_EXP*100.0 / h.CONSUMPTION_EXP),1) c

FROM
HOUSEHOLD_SURVEY h
where survey_year=2012

group by 
Area_code
), result as (


select y2022.area_code CODE,

a||'%' '2022_RATIO',
b||'%' '2017_RATIO',
c||'%' '2012_RATIO',
cast(dense_rank() over (order by a) as integer) as  '2022_RANK',
dense_rank() over (order by b) '2017_RANK',

dense_rank() over (order by c) '2012_RANK'


from y2022 inner join y2017 on y2022.area_code=y2017.area_code inner join y2012 on 
y2022.area_code=y2012.area_code

order by '2022_RANK'
)
select result.*,a.area_name NAME from result inner join AREA a
on result.code=a.area_code
order by 2 asc,a.area_code desc