WITH RANK2022 AS
  (SELECT AREA_CODE,
          ROUND(cast(FOOD_EXP as REAL) /cast(CONSUMPTION_EXP as REAL) *100 ,1) AS HIRITU,
          DENSE_RANK() OVER(ORDER BY ROUND(cast(FOOD_EXP as REAL) /cast(CONSUMPTION_EXP as REAL) *100 ,1) ) AS RANK
     FROM HOUSEHOLD_SURVEY
    WHERE SURVEY_YEAR = '2022') 

, RANK2017 AS
  (SELECT AREA_CODE,
          ROUND(cast(FOOD_EXP as REAL) /cast(CONSUMPTION_EXP as REAL) *100 ,1) AS HIRITU,
          DENSE_RANK() OVER(ORDER BY ROUND(cast(FOOD_EXP as REAL) /cast(CONSUMPTION_EXP as REAL) *100 ,1) ) AS RANK
     FROM HOUSEHOLD_SURVEY
    WHERE SURVEY_YEAR = '2017') 

, RANK2012 AS
  (SELECT AREA_CODE,
          ROUND(cast(FOOD_EXP as REAL) /cast(CONSUMPTION_EXP as REAL) *100 ,1) AS HIRITU,
          DENSE_RANK() OVER(ORDER BY ROUND(cast(FOOD_EXP as REAL) /cast(CONSUMPTION_EXP as REAL) *100 ,1) ) AS RANK
     FROM HOUSEHOLD_SURVEY
    WHERE SURVEY_YEAR = '2012') 

SELECT HS.AREA_CODE AS CODE,
       A.AREA_NAME AS NAME,
       RANK2022.RANK AS "2022_RANK",
       RANK2022.HIRITU ||"%" AS "2022_RATIO",
       RANK2017.RANK AS "2017_RANK",
       RANK2017.HIRITU ||"%" AS "2017_RATIO",
       RANK2012.RANK AS "2012_RANK",
       RANK2012.HIRITU ||"%" AS "2012_RATIO"
  FROM HOUSEHOLD_SURVEY HS
  LEFT JOIN AREA A
   ON A.AREA_CODE = HS.AREA_CODE
  LEFT JOIN RANK2022
    ON RANK2022.AREA_CODE =  HS.AREA_CODE
  LEFT JOIN RANK2017
    ON RANK2017.AREA_CODE =  HS.AREA_CODE
  LEFT JOIN RANK2012
    ON RANK2012.AREA_CODE =  HS.AREA_CODE
 GROUP BY HS.AREA_CODE
 ORDER BY RANK2022.RANK,
          HS.AREA_CODE DESC