WITH population_2015 as (
	select
		t1.SURVEY_YEAR
		, t1.PF_CODE
		, sum(TOTAL_AMT) as TOTAL_AMT
	FROM POPU_TRANSITION as t1
	WHERE t1.SURVEY_YEAR = 2015
	GROUP BY 1,2
)
, population_2020 as (
	select
		t1.SURVEY_YEAR
		, t1.PF_CODE
		, sum(TOTAL_AMT) as TOTAL_AMT
	FROM POPU_TRANSITION as t1
	WHERE t1.SURVEY_YEAR = 2020
	GROUP BY 1,2
)
select
t_2015.PF_CODE as 都道府県コード
, t2.PF_NAME as 都道府県名
, t_2015.TOTAL_AMT as 総人口2015年
, t_2020.TOTAL_AMT as 総人口2020年
, ROUND(
	CAST(t_2020.TOTAL_AMT as real)/ CAST(t_2015.TOTAL_AMT as REAL) * 100, 0) as 人口増加率
from population_2015 as t_2015
LEFT JOIN population_2020 as t_2020
	on t_2015.PF_CODE = t_2020.PF_CODE
LEFT JOIN PREFECTURE as t2
	on t_2015.PF_CODE = t2.PF_CODE
GROUP BY 1,2,3,4,5
HAVING 人口増加率 > 100
ORDER BY 5 desc, 1
;