with pop_2015 as(
    select
        *
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2015
),
pop_2020 as(
    select
        *
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2020
),
pref_data as(
    select
        pref.pf_code,
        pref.pf_name,
        p15.total_amt as total_amt_2015,
        p20.total_amt as total_amt_2020,
        cast(p20.total_amt as float) / cast(p15.total_amt as float ) * 100 as rate
    from
        PREFECTURE pref
        left join
            pop_2015 p15
        on  pref.PF_CODE = p15.PF_CODE
        left join
            pop_2020 p20
        on  pref.PF_CODE = p20.PF_CODE
)
select
    pf_code as 都道府県コード,
    pf_name as 都道府県名,
    total_amt_2015 as 総人口2015年,
    total_amt_2020 as 総人口2020年,
    round(rate) as 人口増加率
from
    pref_data
where rate > 100
order by round(rate) desc, pf_code