with popu_2015 as (
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL_AMT
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2015
)

,popu_2020 as (
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL_AMT
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2020
)

select
    PR.PF_CODE as "都道府県コード"
    ,PR.PF_NAME as "都道府県名"
    ,popu_2015.TOTAL_AMT as "総人口2015年"
    ,popu_2020.TOTAL_AMT as "総人口2020年"
    ,round(cast(popu_2020.TOTAL_AMT as real)/cast(popu_2015.TOTAL_AMT as real)*100,0) as "人口増加率"
    
from
    PREFECTURE as PR
    inner join popu_2015
        on PR.PF_CODE = popu_2015.PF_CODE
    inner join popu_2020
        on PR.PF_CODE = popu_2020.PF_CODE

where
    人口増加率 > 100

order by 
    人口増加率 desc
    ,都道府県コード asc
;