with p15 as (
    select PF_CODE, TOTAL_AMT
    from POPU_TRANSITION
    where SURVEY_YEAR = 2015
),
p20 as (
    select PF_CODE, TOTAL_AMT
    from POPU_TRANSITION
    where SURVEY_YEAR = 2020

)
select
    distinct pp.PF_CODE as '都道府県コード',
    pf.PF_NAME as '都道府県名',
    p15.TOTAL_AMT as '総人口2015年',
    p20.TOTAL_AMT as '総人口2020年',
    round((cast(p20.TOTAL_AMT as real) - cast(p15.TOTAL_AMT as real)) / 1000, 0) as '人口増加率'
from
    POPU_TRANSITION pp
        inner join PREFECTURE pf
            on pp.PF_CODE = pf.PF_CODE
        inner join p15
            on pp.PF_CODE = p15.PF_CODE
        inner join p20
            on pp.PF_CODE = p20.PF_CODE
order by
    人口増加率 desc,
    都道府県コード
;