SELECT
    PREFECTURE.PF_CODE AS "都道府県コード"
    , PREFECTURE.PF_NAME AS "都道府県名"
    , TRANSITION.BEFORE_AMT AS "総人口2015年"
    , TRANSITION.AFTER_AMT AS "総人口2020年"
    , TRANSITION.RATE AS "人口増加率"
FROM
    PREFECTURE
    JOIN
        (
            SELECT
                BEFORE.PF_CODE
                , BEFORE.TOTAL_AMT AS BEFORE_AMT
                , AFTER.TOTAL_AMT AS AFTER_AMT
                , ROUND(AFTER.TOTAL_AMT * 100.0 / BEFORE.TOTAL_AMT) AS RATE
            FROM
                (
                    SELECT
                        PF_CODE, TOTAL_AMT
                    FROM
                        POPU_TRANSITION
                    WHERE
                        SURVEY_YEAR = 2015
                ) AS BEFORE
                JOIN
                (
                    SELECT
                        PF_CODE, TOTAL_AMT
                    FROM
                        POPU_TRANSITION
                    WHERE
                        SURVEY_YEAR = 2020
                ) AS AFTER
                USING (PF_CODE)
            WHERE BEFORE.TOTAL_AMT < AFTER.TOTAL_AMT
        ) AS TRANSITION
        USING (PF_CODE)
ORDER BY
    TRANSITION.RATE DESC
    , PREFECTURE.PF_CODE ASC