WITH BY_YEAR_AMT AS (
    SELECT
        PF_CODE,
        SUM(CASE
            WHEN SURVEY_YEAR = 2015
                THEN TOTAL_AMT
            ELSE 0
            END) AS "2015_AMT",
        SUM(CASE
            WHEN SURVEY_YEAR = 2020
                THEN TOTAL_AMT
            ELSE 0
            END) AS "2020_AMT"   
    FROM
        POPU_TRANSITION
    GROUP BY
        PF_CODE
    )
SELECT
    t1.PF_CODE AS "都道府県コード",
    t2.PF_NAME AS "都道府県名",
    t1."2015_AMT" AS "総人口2015年",
    t1."2020_AMT" AS "総人口2020年",
    ROUND(CAST(t1."2020_AMT" AS REAL)/CAST(t1."2015_AMT" AS REAL)*100,0) AS "人口増加率"
FROM
    BY_YEAR_AMT AS t1
LEFT JOIN PREFECTURE AS t2
    ON t1.PF_CODE = t2.PF_CODE
WHERE
    人口増加率 > 100
ORDER BY
    人口増加率 DESC,
    都道府県コード ASC
;