WITH SURVEY_2022 AS
(
  SELECT
    AREA_CODE,
    RATIO || '%' AS RATIO,
    DENSE_RANK() OVER(ORDER BY RATIO) AS RANK
  FROM
    (
      SELECT
        AREA_CODE,
        ROUND(CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL) * 100, 1) AS RATIO
      FROM
        HOUSEHOLD_SURVEY
      WHERE
        SURVEY_YEAR = 2022
    )
),
SURVEY_2017 AS
(
  SELECT
    AREA_CODE,
    RATIO || '%' AS RATIO,
    DENSE_RANK() OVER(ORDER BY RATIO) AS RANK
  FROM
    (
      SELECT
        AREA_CODE,
        ROUND(CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL) * 100, 1) AS RATIO
      FROM
        HOUSEHOLD_SURVEY
      WHERE
        SURVEY_YEAR = 2017
    )
),
SURVEY_2012 AS
(
  SELECT
    AREA_CODE,
    RATIO || '%' AS RATIO,
    DENSE_RANK() OVER(ORDER BY RATIO) AS RANK
  FROM
    (
      SELECT
        AREA_CODE,
        ROUND(CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL) * 100, 1) AS RATIO
      FROM
        HOUSEHOLD_SURVEY
      WHERE
        SURVEY_YEAR = 2012
    )
)
SELECT
  S2022.AREA_CODE AS CODE,
  A.AREA_NAME AS NAME,
  S2022.RANK AS '2022_RANK',
  S2022.RATIO AS '2022_RATIO',
  S2017.RANK AS '2017_RANK',
  S2017.RATIO AS '2017_RATIO',
  S2012.RANK AS '2012_RANK',
  S2012.RATIO AS '2012_RATIO'
FROM
  (
    (
      SURVEY_2022 AS 'S2022'
      INNER JOIN
        AREA AS A
      ON
        S2022.AREA_CODE = A.AREA_CODE
    )
    LEFT OUTER JOIN
      SURVEY_2017 AS 'S2017'
    ON
      S2022.AREA_CODE = S2017.AREA_CODE
  )
  LEFT OUTER JOIN
    SURVEY_2012 AS 'S2012'
  ON
    S2022.AREA_CODE = S2012.AREA_CODE
ORDER BY
  S2022.RANK ASC,
  S2022.AREA_CODE DESC