with
result2015 as (
    select * from popu_transition where survey_year = 2015
),
result2020 as (
    select * from popu_transition where survey_year = 2020
),
result as (
    select
        result2015.pf_code,
        result2015.total_amt as total_2015,
        result2020.total_amt as total_2020,
        round(result2020.total_amt*100.0/result2015.total_amt) as ratio
    from result2015 join result2020 on result2015.pf_code = result2020.pf_code
)
select
    result.pf_code as "都道府県コード",
    pf_name as "都道府県名",
    total_2015 as "総人口2015年",
    total_2020 as "総人口2020年",
    ratio as "人口増加率"
from result join prefecture on result.pf_code = prefecture.pf_code where ratio > 100 order by ratio desc, result.pf_code asc;