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
;
CREATE TEMPORARY TABLE TOTAL_AMT_CHANGE AS
    SELECT
        TOTAL_AMT_2015.PF_CODE
        ,TOTAL_AMT_2015.TOTAL_AMT_YEAR as 総人口2015年
        ,TOTAL_AMT_2020.TOTAL_AMT_YEAR as 総人口2020年
        ,CAST(TOTAL_AMT_2020.TOTAL_AMT_YEAR AS REAL) / TOTAL_AMT_2015.TOTAL_AMT_YEAR * 100 as 人口増加率
    FROM
        TOTAL_AMT_2015
    LEFT JOIN TOTAL_AMT_2020 ON TOTAL_AMT_2015.PF_CODE == TOTAL_AMT_2020.PF_CODE
;

SELECT
    PREFECTURE.PF_CODE AS 都道府県コード
    ,PREFECTURE.PF_NAME as 都道府県名
    ,TOTAL_AMT_CHANGE.総人口2015年
    ,TOTAL_AMT_CHANGE.総人口2020年
    ,ROUND(TOTAL_AMT_CHANGE.人口増加率)
FROM
    PREFECTURE
LEFT JOIN TOTAL_AMT_CHANGE ON PREFECTURE.PF_CODE == TOTAL_AMT_CHANGE.PF_CODE
WHERE TOTAL_AMT_CHANGE.人口増加率 > 100
ORDER BY
    人口増加率 DESC
    ,都道府県コード