WITH pivot AS(
    SELECT 
        PF_CODE,
        MAX(CASE WHEN SURVEY_YEAR = 2015 THEN TOTAL_AMT END) AS 'be',
        MAX(CASE WHEN SURVEY_YEAR = 2020 THEN TOTAL_AMT END) AS 'af'
    FROM 
        POPU_TRANSITION
    GROUP BY 
        PF_CODE
)
,
inc_rate AS(
    SELECT
        PF_CODE,
        be,
        af,
        ROUND(CAST(af AS REAL)*100/CAST(be AS REAL)) AS inc_rate
    FROM
        pivot
)
SELECT
    pr.PF_CODE AS '都道府県コード',
    pr.PF_NAME AS '都道府県名',
    ir.be AS '総人口2015年',
    ir.af AS '総人口2020年',
    ir.inc_rate AS '人口増加率'
FROM
    inc_rate AS ir
    INNER JOIN PREFECTURE AS pr 
    ON ir.PF_CODE = pr.PF_CODE
WHERE
    ir.inc_rate > 100
ORDER BY
    人口増加率 DESC,
    都道府県コード ASC
;