with HI_22 AS(
 -- 2022年
 SELECT H_2022,HI22a.AREA_CODE,AREA_NAME,
 -- 順位付け
    dense_rank() over (order by H_2022 asc) as JUN
 FROM(
    SELECT
 -- 比率を算出（小数第一位）    
    round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL)*100,1) as H_2022
    ,HOUSEHOLD_SURVEY.AREA_CODE,AREA_NAME
    FROM HOUSEHOLD_SURVEY
    INNER JOIN AREA
        ON AREA.AREA_CODE = HOUSEHOLD_SURVEY.AREA_CODE
    WHERE SURVEY_YEAR = 2022
    ) as HI22a
)
,HI_17 AS(
 -- 2017年
 SELECT H_2017,HI17a.AREA_CODE,
    dense_rank() over (order by H_2017 asc) as JUN
 FROM(
    SELECT
    round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL)*100,1) as H_2017
    ,HOUSEHOLD_SURVEY.AREA_CODE
    FROM HOUSEHOLD_SURVEY
    WHERE SURVEY_YEAR = 2017
    ) as HI17a
)
,HI_12 AS(
 -- 2012年
 SELECT H_2012,HI12a.AREA_CODE,
    dense_rank() over (order by H_2012 asc) as JUN
 FROM(
    SELECT
    round(cast(FOOD_EXP as REAL) / cast(CONSUMPTION_EXP as REAL)*100,1) as H_2012
    ,HOUSEHOLD_SURVEY.AREA_CODE
    FROM HOUSEHOLD_SURVEY
    WHERE SURVEY_YEAR = 2012
    ) as HI12a
)

SELECT 
    CODE,NAME,
    "2022_RANK","2022_RATIO",
    "2017_RANK","2017_RATIO",
    "2012_RANK","2012_RATIO"
FROM (
    SELECT
        HI_22.AREA_CODE AS CODE
    ,   HI_22.AREA_NAME AS NAME
    ,   HI_22.JUN AS "2022_RANK"
    ,   HI_22.H_2022 || '%' AS "2022_RATIO"
    ,   HI_17.JUN AS "2017_RANK"
    ,   HI_17.H_2017 || '%' AS "2017_RATIO"
    ,   HI_12.JUN AS "2012_RANK"
    ,   HI_12.H_2012 || '%' AS "2012_RATIO"
     FROM HI_22
        INNER JOIN HI_17
         ON HI_17.AREA_CODE = HI_22.AREA_CODE
        INNER JOIN HI_12
         ON HI_12.AREA_CODE = HI_22.AREA_CODE
) as a
ORDER BY
       "2022_RANK" ASC
      ,CODE DESC
;