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