with 
st_2022 as (
select
  AREA_CODE
  , ROUND((CAST(FOOD_EXP AS REAL)  / CONSUMPTION_EXP * 100), 1) as RATIO
  , dense_rank() over (order by ROUND(CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100), 1) as MY_RANK
from
  household_survey
where survey_year = 2022
)
,
st_2017 as (
select
  AREA_CODE
  , ROUND((CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100), 1) as RATIO
  , dense_rank() over (order by ROUND(CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100), 1) as MY_RANK
from
  household_survey
where survey_year = 2017
)
,
st_2012 as (
select
  AREA_CODE
  , ROUND((CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100), 1) as RATIO
  , dense_rank() over (order by ROUND(CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100), 1) as MY_RANK
from
  household_survey
where survey_year = 2012
)
select st_2022.AREA_CODE as CODE, area.AREA_NAME as NAME,  st_2022.MY_RANK as "2022_RANK", st_2022.RATIO || '%' as "2022_RATIO", st_2017.MY_RANK as "2017_RANK", st_2017.RATIO || '%' as "2017_RATIO", st_2012.MY_RANK as "2012_RANK", st_2012.RATIO || '%' as "2012_RATIO"
from st_2022 join st_2012 on st_2022.AREA_CODE = st_2012.AREA_CODE
join st_2017 on st_2022.AREA_CODE = st_2017.AREA_CODE
join area on st_2022.AREA_CODE = area.AREA_CODE
order by "2022_RANK", CODE desc
;