WITH INC AS (
SELECT POPU_2015.PF_CODE AS PF_CODE, POPU_2015.TOTAL_AMT AS AMT_2015, POPU_2020.TOTAL_AMT AS AMT_2020, ROUND(100.0 * POPU_2020.TOTAL_AMT / POPU_2015.TOTAL_AMT, 0) AS RATE
FROM POPU_TRANSITION AS POPU_2015
INNER JOIN POPU_TRANSITION AS POPU_2020
ON POPU_2015.PF_CODE = POPU_2020.PF_CODE
WHERE POPU_2015.SURVEY_YEAR = 2015 AND POPU_2020.SURVEY_YEAR = 2020
AND POPU_2015.TOTAL_AMT < POPU_2020.TOTAL_AMT
)
SELECT
INC.PF_CODE AS '都道府県コード',
PF_NAME AS '都道府県名',
AMT_2015 AS '総人口2015年',
AMT_2020 AS '総人口2020年',
RATE AS '人口増加率'
FROM INC
INNER JOIN PREFECTURE
ON PREFECTURE.PF_CODE = INC.PF_CODE
ORDER BY RATE DESC, INC.PF_CODE ASC