with popu_2015 as
(
    select
        PF_CODE,
        TOTAL_AMT
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2015
),
popu_2020 as
(
    select
        PF_CODE,
        TOTAL_AMT
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2020
)
select
    PREFECTURE.PF_CODE as "都道府県コード",
    PREFECTURE.PF_NAME as "都道府県名",
    popu_2015.TOTAL_AMT as "総人口2015年",
    popu_2020.TOTAL_AMT as "総人口2020年",
    100 + round(
        ((cast(popu_2020.TOTAL_AMT as real) - cast(popu_2015.TOTAL_AMT as real))
        / cast(popu_2015.TOTAL_AMT as real))* 100,
    0) as "人口増加率"
from
    popu_2015
inner join
    popu_2020
on
    popu_2015.PF_CODE = popu_2020.PF_CODE
inner join
    PREFECTURE
on
    PREFECTURE.PF_CODE = popu_2020.PF_CODE
order by
    `人口増加率` desc,
    PREFECTURE.PF_CODE asc
;