WITH popu_transition_2015 AS (
    SELECT
        PF_CODE
        , TOTAL_AMT AS total_amt_2015
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2015
)
, popu_transition_2020 AS (
    SELECT
        PF_CODE
        , TOTAL_AMT AS total_amt_2020
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2020
)

SELECT
    popu_transition_2015.PF_CODE AS '都道府県コード'
    , PF_NAME AS '都道府県名'
    , total_amt_2015 AS '総人口2015年'
    , total_amt_2020 AS '総人口2020年'
    , ROUND(total_amt_2020 * 100.0 / total_amt_2015) AS '人口増加率'
FROM
    popu_transition_2015
JOIN
    popu_transition_2020 ON popu_transition_2015.PF_CODE = popu_transition_2020.PF_CODE
    , PREFECTURE ON popu_transition_2015.PF_CODE = PREFECTURE.PF_CODE
WHERE
    (total_amt_2020 - total_amt_2015) > 0   -- 2015年から2020年の期間で人口が増加
ORDER BY
    ROUND(total_amt_2020 * 100.0 / total_amt_2015) DESC
    , popu_transition_2015.PF_CODE ASC
    -- SQLiteのバージョンによっては、エイリアスではソートできないため注意
;