with before as (
	select PF_CODE as PF_CODE_before
		,TOTAL_AMT as AMT_before
	from POPU_TRANSITION
	where SURVEY_YEAR = 2015
	)
,after as (
	select PF_CODE as PF_CODE_after
		,TOTAL_AMT as AMT_after
	from POPU_TRANSITION
	where SURVEY_YEAR = 2020
	)
select after.PF_CODE_after as 都道府県コード
	,PREFECTURE.PF_NAME as 都道府県名
	,before.AMT_before as 総人口2015年
	,after.AMT_after as 総人口2020年
	,round(cast(after.AMT_after as real)/cast(before.AMT_before as real) * 100,0) as 人口増加率
from after 
left outer join before on (PF_CODE_after = PF_CODE_before)
left outer join PREFECTURE on (PF_CODE_after = PF_CODE)
where (総人口2020年 - 総人口2015年) >= 1 and 総人口2015年 >= 1 and 総人口2020年 >= 1 
order by 人口増加率 desc,都道府県コード