SELECT 
	p.PF_CODE as '都道府県コード'
	,p.PF_NAME as '都道府県名'
    ,t1.t2015 as '総人口2015年'
    ,t2.t2020 as '総人口2020年'
    ,ROUND(((1.0*t2.t2020 / t1.t2015))*100) as '人口増加率'
FROM
	PREFECTURE as p
INNER JOIN (
    SELECT 
        PF_CODE
        ,sum(TOTAL_AMT) as t2015
    FROM
        POPU_TRANSITION
    WHERE SURVEY_YEAR = '2015'
    GROUP BY 
        SURVEY_YEAR
        ,PF_CODE
    ) as t1
ON p.PF_CODE = t1.PF_CODE
INNER JOIN (
    SELECT 
        PF_CODE
        ,sum(TOTAL_AMT) as t2020
    FROM
        POPU_TRANSITION
    WHERE SURVEY_YEAR = '2020'
    GROUP BY 
        SURVEY_YEAR
        ,PF_CODE
    ) as t2
ON p.PF_CODE = t2.PF_CODE
WHERE t1.t2015 < t2.t2020
ORDER BY 
    ROUND(((1.0*t2.t2020 / t1.t2015))*100) desc
    ,p.PF_CODE
;