WITH Y2022 AS (
  SELECT AREA_CODE
        ,ROUND(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 1.0 * 100 , 1) RATIO
        ,DENSE_RANK() OVER (ORDER BY ROUND(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 1.0 * 100 , 1)) RNK
    FROM (SELECT *
            FROM HOUSEHOLD_SURVEY
           WHERE SURVEY_YEAR = 2022)
), Y2017 AS (
  SELECT AREA_CODE
        ,ROUND(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 1.0 * 100 , 1) RATIO
        ,DENSE_RANK() OVER (ORDER BY ROUND(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 1.0 * 100 , 1)) RNK
    FROM (SELECT *
            FROM HOUSEHOLD_SURVEY
           WHERE SURVEY_YEAR = 2017)
), Y2012 AS (
  SELECT AREA_CODE
        ,ROUND(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 1.0 * 100 , 1) RATIO
        ,DENSE_RANK() OVER (ORDER BY ROUND(FOOD_EXP * 1.0 / CONSUMPTION_EXP * 1.0 * 100 , 1)) RNK
    FROM (SELECT *
            FROM HOUSEHOLD_SURVEY
           WHERE SURVEY_YEAR = 2012)
)
SELECT AREA.AREA_CODE CODE
      ,AREA.AREA_NAME NAME
      ,Y22.RNK AS '2022_RANK'
      ,Y22.RATIO || '%' AS '2022_RATIO'
      ,Y17.RNK '2017_RANK'
      ,Y17.RATIO || '%' '2017_RATIO'
      ,Y12.RNK '2012_RANK'
      ,Y12.RATIO || '%' '2012_RATIO'
  FROM AREA
  LEFT OUTER JOIN Y2022 Y22
    ON AREA.AREA_CODE = Y22.AREA_CODE
  LEFT OUTER JOIN Y2017 Y17
    ON AREA.AREA_CODE = Y17.AREA_CODE
  LEFT OUTER JOIN Y2012 Y12
    ON AREA.AREA_CODE = Y12.AREA_CODE
ORDER BY Y22.RNK
        ,AREA.AREA_CODE DESC