WITH
  TMP2022 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) asc
      ) AS RANK
    FROM
      HOUSEHOLD_SURVEY
    WHERE
      SURVEY_YEAR = 2022
  ),
  TMP2017 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) asc
      ) AS RANK
    FROM
      HOUSEHOLD_SURVEY
    WHERE
      SURVEY_YEAR = 2017
  ),
  TMP2012 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) asc
      ) AS RANK
    FROM
      HOUSEHOLD_SURVEY
    WHERE
      SURVEY_YEAR = 2012
  )
SELECT
  area.AREA_CODE AS CODE,
  area.AREA_NAME AS NAME,
  TMP2022.RANK AS "2022_RANK",
  TMP2022.RATIO AS "2022_RATIO",
  TMP2017.RANK AS "2017_RANK",
  TMP2017.RATIO AS "2017_RATIO",
  TMP2012.RANK AS "2012_RANK",
  TMP2012.RATIO AS "2012_RATIO"
FROM
  AREA
  LEFT OUTER JOIN TMP2022 ON AREA.AREA_CODE = TMP2022.AREA_CODE
  LEFT OUTER JOIN TMP2017 ON AREA.AREA_CODE = TMP2017.AREA_CODE
  LEFT OUTER JOIN TMP2012 ON AREA.AREA_CODE = TMP2012.AREA_CODE
ORDER BY
  TMP2022.RANK asc,
  AREA.AREA_CODE desc;