with AMT_2015 as (
    select 
        po.PF_CODE ,
        pr.PF_NAME ,
        po.TOTAL_AMT  
    from    
        POPU_TRANSITION po
    left join
        PREFECTURE pr
    on po.PF_CODE = pr.PF_CODE
    
    where
        po.SURVEY_YEAR = 2015
),
AMT_2020 as (
    select 
        po.PF_CODE,
        pr.PF_NAME,
        po.TOTAL_AMT 
    from    
        POPU_TRANSITION po
    left join
        PREFECTURE pr
    on po.PF_CODE = pr.PF_CODE
    
    where
        po.SURVEY_YEAR = 2020
)
select 
    AMT_15.PF_CODE as '都道府県コード',
    AMT_15.PF_NAME as '都道府県名',
    AMT_15.TOTAL_AMT as '総人口2015年',
    AMT_20.TOTAL_AMT as '総人口2020年',
    round(
    cast(AMT_20.TOTAL_AMT as real) / cast(AMT_15.TOTAL_AMT as real) * 100
    ) as '人口増加率'
from 
    AMT_2015 AMT_15
inner join
    AMT_2020 AMT_20
on 
    AMT_15.PF_CODE = AMT_20.PF_CODE
where
    人口増加率 > 100
order by 
    人口増加率 desc,
    都道府県コード asc