WITH transition AS (
    SELECT
        popu_transition.pf_code AS '都道府県コード',
        pf_name AS '都道府県名',
        max(
            CASE
                WHEN survey_year = 2015 THEN total_amt
                ELSE NULL
            END
        ) AS '総人口2015年',
        max(
            CASE
                WHEN survey_year = 2020 THEN total_amt
                ELSE NULL
            END
        ) AS '総人口2020年'
    FROM
        popu_transition
        INNER JOIN prefecture ON popu_transition.pf_code = prefecture.pf_code
    GROUP BY
        popu_transition.pf_code
)
SELECT
    *,
    round(
        cast(`総人口2020年` AS real) / cast(`総人口2015年` AS real) * 100.0,
        0
    ) AS '人口増加率'
FROM
    transition
WHERE
    `総人口2020年` > `総人口2015年`
ORDER BY
    `人口増加率` DESC