select
 p.PF_CODE as 都道府県コード
 , p.PF_NAME as 都道府県名
 , t1.TOTAL_AMT as 総人口2015年
 , t2.TOTAL_AMT as 総人口2020年
 , round((cast(t1.TOTAL_AMT as real) / cast(t2.TOTAL_AMT as real)) *100) as 人口増加率
from PREFECTURE p
join (
 select
  p.PF_CODE as PF_CODE
  , pt.TOTAL_AMT as TOTAL_AMT
  , pt.SURVEY_YEAR
 from PREFECTURE p
 join POPU_TRANSITION pt on p.PF_CODE = pt.PF_CODE
 where pt.SURVEY_YEAR = 2015
) t1 on p.PF_CODE = t1.PF_CODE
join (
 select
  p.PF_CODE as PF_CODE
  , pt.TOTAL_AMT as TOTAL_AMT
  , pt.SURVEY_YEAR
 from PREFECTURE p
 join POPU_TRANSITION pt on p.PF_CODE = pt.PF_CODE
 where pt.SURVEY_YEAR in (2016, 2017, 2018, 2019, 2020)
) t2 on t1.PF_CODE = t2.PF_CODE
join POPU_TRANSITION pt on p.PF_CODE = pt.PF_CODE
where t1.TOTAL_AMT < t2.TOTAL_AMT
group by 都道府県コード
order by 人口増加率 DESC, 都道府県コード;