WITH MAIN_2022 AS ( 
    -- 2022年のデータを集計
    SELECT
        AREA_CODE
        , ROUND( 
            -- REAL型に変換して計算
            CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
            , 1
        ) AS RATIO
          -- DENSE_RANK関数で、比率の昇順で並べて欠番なしで採番をおこなう
        , 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 ( 
    -- 上記と同様に2017年のデータを集計
    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
) 
, SUB_2012 AS ( 
    -- 上記と同様に2012年のデータを集計
    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
    , 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 
        ON AREA.AREA_CODE = M22.AREA_CODE 
ORDER BY
    M22.RANK
    , M22.AREA_CODE DESC;