CREATE TEMPORARY TABLE TOTAL_AMT_2015 AS
    SELECT
        PF_CODE
        ,SUM(TOTAL_AMT) AS TOTAL_AMT_YEAR
    FROM
        POPU_TRANSITION
    WHERE SURVEY_YEAR == 2015
    GROUP BY PF_CODE
;

CREATE TEMPORARY TABLE TOTAL_AMT_2020 AS
    SELECT
        PF_CODE
        ,SUM(TOTAL_AMT) AS TOTAL_AMT_YEAR
    FROM
        POPU_TRANSITION
    WHERE SURVEY_YEAR == 2020
    GROUP BY PF_CODE
;

SELECT
    PREFECTURE.PF_CODE AS 都道府県コード
    ,PREFECTURE.PF_NAME as 都道府県名
    ,TOTAL_AMT_2015.TOTAL_AMT_YEAR as 総人口2015年
    ,TOTAL_AMT_2020.TOTAL_AMT_YEAR as 総人口2020年
    ,ROUND(
        CAST(TOTAL_AMT_2020.TOTAL_AMT_YEAR AS REAL) / TOTAL_AMT_2015.TOTAL_AMT_YEAR * 100
    ) as 人口増加率
FROM
    PREFECTURE
INNER JOIN TOTAL_AMT_2015 ON PREFECTURE.PF_CODE == TOTAL_AMT_2015.PF_CODE
INNER JOIN TOTAL_AMT_2020 ON PREFECTURE.PF_CODE == TOTAL_AMT_2020.PF_CODE
WHERE 人口増加率 >= 100
ORDER BY
    人口増加率 DESC
    ,都道府県コード