with ta as (select SURVEY_YEAR, AREA_CODE, round(FOOD_EXP * 100.0 / CONSUMPTION_EXP,1) as RATIO
from HOUSEHOLD_SURVEY)
, tb as (
select *, dense_rank() over(partition by SURVEY_YEAR order by RATIO) as RANK,  case when substr(cast(RATIO as string),-2,1) = "." then cast(RATIO as string) || "%" else cast(RATIO as string) || ".0%" end as RATIO_1
from ta)


select AREA_CODE as CODE, AREA_NAME as NAME, `2022_RANK`,`2022_RATIO`,`2017_RANK`,`2017_RATIO`,`2012_RANK`,`2012_RATIO`
from (select AREA_CODE,RATIO_1 as `2022_RATIO`,RANK as `2022_RANK`  from tb where SURVEY_YEAR = 2022)
left join (select AREA_CODE,RATIO_1 as `2017_RATIO`,RANK as `2017_RANK`  from tb where SURVEY_YEAR = 2017) using (AREA_CODE)
left join (select AREA_CODE,RATIO_1 as `2012_RATIO`,RANK as `2012_RANK`  from tb where SURVEY_YEAR = 2012) using (AREA_CODE)
left join AREA using (AREA_CODE)
order by `2022_RANK` asc, CODE desc