with hs22 as (
select * from HOUSEHOLD_SURVEY
where SURVEY_YEAR = '2022'
),
hs17 as (
select * from HOUSEHOLD_SURVEY
where SURVEY_YEAR = '2017'
),
hs12 as (
select * from HOUSEHOLD_SURVEY
where SURVEY_YEAR = '2012'
),
sel as (
select
area_code as CODE,
area_name as NAME,
round(cast(hs22.FOOD_EXP as REAL) / cast(hs22.CONSUMPTION_EXP as REAL) * 100, 1) as r22,
round(cast(hs17.FOOD_EXP as REAL) / cast(hs17.CONSUMPTION_EXP as REAL) * 100, 1) as r17,
round(cast(hs12.FOOD_EXP as REAL) / cast(hs12.CONSUMPTION_EXP as REAL) * 100, 1) as r12
from
area a
join hs22 using (area_code)
join hs17 using (area_code)
join hs12 using (area_code)
)
select
CODE, NAME,
dense_rank() over (order by r22) as '2022_RANK',
r22 || '%' as '2022_RATIO',
dense_rank() over (order by r17) as '2017_RANK',
r17 || '%' as '2017_RATIO',
dense_rank() over (order by r12) as '2012_RANK',
r12 || '%' as '2012_RATIO'
from sel join hs22 on (sel.code = hs22.area_code)
order by "2022_rank" asc, code desc
;