WITH pop2015 AS (
    SELECT
        *
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2015
)
, pop2020 AS (
    SELECT
        *
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2020
)
SELECT
    p.pf_code AS 都道府県コード
    ,p.pf_name AS 都道府県名
    ,pop2015.total_amt AS 総人口2015年
    ,pop2020.total_amt AS 総人口2020年
    ,ROUND(CAST(pop2020.total_amt AS REAL)/ CAST(pop2015.total_amt AS REAL) * 100, 0) AS 人口増加率
FROM
    PREFECTURE AS p
LEFT JOIN
    pop2015
ON
    p.pf_code = pop2015.pf_code
LEFT JOIN
    pop2020
ON
    p.pf_code = pop2020.pf_code
WHERE
    "人口増加率" > 100
ORDER BY
    "人口増加率" DESC
    ,都道府県コード ASC
;