WITH TEKITO AS
(SELECT 
PF_CODE AS 都道府県コード,
PF_NAME AS 都道府県名,
TOTAL_AMT AS 総人口2020年,
LEAD(TOTAL_AMT)
OVER(PARTITION BY PF_CODE ORDER BY SURVEY_YEAR DESC) AS 総人口2015年,
CAST(TOTAL_AMT AS FLOAT)*100 / LEAD(TOTAL_AMT)
OVER(PARTITION BY PF_CODE ORDER BY SURVEY_YEAR DESC) AS 人口増加率
FROM POPU_TRANSITION
JOIN PREFECTURE
USING(PF_CODE)
WHERE SURVEY_YEAR IN (2015,2020))
SELECT
都道府県コード,都道府県名,総人口2020年,総人口2015年,ROUND(人口増加率)
FROM TEKITO
WHERE 人口増加率 > 100
ORDER BY 5 DESC,1;