select 
	PF_CODE_2015 as 都道府県コード,
	PF_NAME as 都道府県名	,
	TOTAL_AMT_2015 as 総人口2015年,
	TOTAL_AMT_2020 as 総人口2020年,
	ratio as 人口増加率
from 
	(select 
		d_2015.PF_CODE as PF_CODE_2015,
		d_2015.TOTAL_AMT as TOTAL_AMT_2015,
		d_2020.PF_CODE as PF_CODE_2020,
		d_2020.TOTAL_AMT as TOTAL_AMT_2020 ,
		round((CAST(d_2020.TOTAL_AMT as REAL) / CAST(d_2015.TOTAL_AMT as REAL)) * 100 ,0) as ratio
	from 
		(select * from POPU_TRANSITION where SURVEY_YEAR = '2015') as d_2015
	left join 
		(select * from POPU_TRANSITION where SURVEY_YEAR = '2020') as d_2020
	on
		d_2015.PF_CODE = d_2020.PF_CODE
	where 
	    d_2015.TOTAL_AMT < d_2020.TOTAL_AMT
	) as a
left join
	PREFECTURE as b 
on a.PF_CODE_2015 = b.PF_CODE
order by 人口増加率 desc ,都道府県コード asc ;