WITH MAIN_2022 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
  ),

SUB_2017 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 = 2027
  ),

SUB_2012 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
M22.AREA_CODE AS CODE,
A.AREA_NAME AS NAME,
M22.RANK AS '2022_RANK',
M22.RATIO || '%' AS '2022_RATIO',
S17.RANK AS '2017_RANK',
S17.RATIO || '%' AS '2017_RATIO',
S12.RANK AS '2012_RANK',
S12.RATIO || '%' AS '2012_RATIO'

FROM MAIN_2022 AS M22
 INNER JOIN SUB_2017 AS S17
  ON S17.AREA_CODE = M22.AREA_CODE
 INNER JOIN SUB_2012 AS S12
  ON S12.AREA_CODE = M22.AREA_CODE
 INNER JOIN AREA AS A
  ON A.AREA_CODE = M22.AREA_CODE
  
ORDER BY
 M22.RANK ASC,
 M22.AREA_CODE DESC;