select
	t2.PF_CODE as 都道府県コード,
	t2.PF_NAME as 都道府県名,
	総人口2015年,
	総人口2020年,
	人口増加率
from
	(
		select
			PF_CODE,
			TOTAL_AMT_2015 as 総人口2015年,
			TOTAL_AMT_2020 as 総人口2020年,
			round(cast(TOTAL_AMT_2020 as float) / TOTAL_AMT_2015 * 100,0) as 人口増加率
		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
		)
	) t1
inner join
    PREFECTURE t2
    on  t1.PF_CODE = t2.PF_CODE
where
	人口増加率 > 100
order by 5 desc, 1
;