WITH SUB AS(
    SELECT
        PF_CODE,
        TOTAL_AMT
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR=2020
    )

SELECT
    PT.PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    PT.TOTAL_AMT AS '総人口2015年',
    SUB.TOTAL_AMT AS '総人口2020年',
    ROUND(
        CAST(SUB.TOTAL_AMT AS REAL)/CAST(PT.TOTAL_AMT AS REAL)*100
        ,0) AS '人口増加率'
FROM
    POPU_TRANSITION AS PT
    INNER JOIN PREFECTURE
        ON PT.PF_CODE = PREFECTURE.PF_CODE
    INNER JOIN SUB
        ON PT.PF_CODE = SUB.PF_CODE
WHERE
    SURVEY_YEAR = 2015
    AND SUB.TOTAL_AMT > PT.TOTAL_AMT
ORDER BY
    人口増加率 DESC,
    都道府県コード ASC
    ;