with ratio2022 as (
select AREA_CODE
      ,DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP as double) / CAST(CONSUMPTION_EXP as double) * 100 ,1)) as '2022_RANK'
      ,ROUND(CAST(FOOD_EXP as double) / CAST(CONSUMPTION_EXP as double) * 100 ,1) || '%' as '2022_RATIO'
  from HOUSEHOLD_SURVEY
  where SURVEY_YEAR = 2022
  group by AREA_CODE
)

,ratio2017 as (
select AREA_CODE
      ,DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP as double) / CAST(CONSUMPTION_EXP as double) * 100 ,1)) as '2017_RANK'
      ,ROUND(CAST(FOOD_EXP as double) / CAST(CONSUMPTION_EXP as double) * 100 ,1) || '%' as '2017_RATIO'
  from HOUSEHOLD_SURVEY
  where SURVEY_YEAR = 2017
  group by AREA_CODE
)

,ratio2012 as (
select AREA_CODE
      ,DENSE_RANK() OVER(ORDER BY ROUND(CAST(FOOD_EXP as double) / CAST(CONSUMPTION_EXP as double) * 100 ,1)) as '2012_RANK'
      ,ROUND(CAST(FOOD_EXP as double) / CAST(CONSUMPTION_EXP as double) * 100 ,1) || '%' as '2012_RATIO'
  from HOUSEHOLD_SURVEY
  where SURVEY_YEAR = 2012
  group by AREA_CODE
)

select AREA.AREA_CODE as CODE
      ,AREA.AREA_NAME as NAME
      ,ratio2022.'2022_RANK'
      ,ratio2022.'2022_RATIO'
      ,ratio2017.'2017_RANK'
      ,ratio2017.'2017_RATIO'
      ,ratio2012.'2012_RANK'
      ,ratio2012.'2012_RATIO'
  from ratio2022
    inner join ratio2017 
      on ratio2022.AREA_CODE = ratio2017.AREA_CODE
    inner join ratio2012 
      on ratio2022.AREA_CODE = ratio2012.AREA_CODE
    inner join AREA
      on ratio2022.AREA_CODE = AREA.AREA_CODE
  order by ratio2022.'2022_RANK', ratio2022.AREA_CODE desc
    ;