WITH pop2015 AS (
    SELECT
        PF_CODE,
        TOTAL_AMT
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2015
),
pop2020 AS (
    SELECT
        PF_CODE,
        TOTAL_AMT
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2020
),
pop AS (
    SELECT
        pop2015.PF_CODE AS PF_CODE,
        pop2015.TOTAL_AMT AS 総人口2015年,
        pop2020.TOTAL_AMT AS 総人口2020年,
        ROUND(CAST(pop2020.TOTAL_AMT AS REAL) / CAST(pop2015.TOTAL_AMT AS REAL) * 100) AS 人口増加率
    FROM 
        pop2015,
        pop2020
    WHERE
        pop2015.PF_CODE = pop2020.PF_CODE
)

SELECT
    p.PF_CODE AS 都道府県コード,
    p.PF_NAME AS 都道府県名,
    pop.総人口2015年,
    pop.総人口2020年,
    pop.人口増加率
FROM 
    PREFECTURE AS p,
    pop
WHERE
    p.PF_CODE = pop.PF_CODE AND
    pop.人口増加率 > 100
ORDER BY
    pop.人口増加率 DESC,
    都道府県コード ASC