WITH POPU_2015 AS (
    SELECT 
        PF_CODE,
        TOTAL_AMT
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2015
),
POPU_2020 AS (
    SELECT 
        PF_CODE,
        TOTAL_AMT
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2020
)
SELECT
    PREFECTURE.PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    POPU_2015.TOTAL_AMT AS 総人口2015年,
    POPU_2020.TOTAL_AMT AS 総人口2020年,
    ROUND(CAST(POPU_2020.TOTAL_AMT AS REAL) / CAST(POPU_2015.TOTAL_AMT AS REAL) * 100) AS 人口増加率
FROM
    POPU_2015
LEFT JOIN
    POPU_2020 ON POPU_2015.PF_CODE = POPU_2020.PF_CODE
LEFT JOIN
    PREFECTURE ON POPU_2015.PF_CODE = PREFECTURE.PF_CODE
WHERE
    人口増加率 >= 100
ORDER BY 
    人口増加率 DESC,
    都道府県コード ASC;