select
	t2.PF_CODE as 都道府県コード,
	t2.PF_NAME as 都道府県名,
	t1.TOTAL_AMT_2015 as 総人口2015年,
	t1.TOTAL_AMT_2020 as 総人口2020年,
	t1.grouth_rate as 人口増加率
from
	(
		select
			PF_CODE,
			TOTAL_AMT_2015,
			TOTAL_AMT_2020,
			round(cast(TOTAL_AMT_2020 as float) / TOTAL_AMT_2015 * 100,0) as grouth_rate
		from
		(
			select
				PF_CODE,
				MAX(case when SURVEY_YEAR = 2015 then TOTAL_AMT else NULL end) as TOTAL_AMT_2015,
				MAX(case when SURVEY_YEAR = 2020 then TOTAL_AMT else NULL end) as TOTAL_AMT_2020
			from
				POPU_TRANSITION
			group by 1
		)
		where
			TOTAL_AMT_2015 < TOTAL_AMT_2020
	) t1
inner join
    PREFECTURE t2
    on  t1.PF_CODE = t2.PF_CODE
order by 5 desc, 1
;