with t_2015 as (
    select
        *
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2015
),
t_2020 as (
    select
        * 
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2020
)
select
    t.PF_CODE as 都道府県コード,
    p.PF_NAME as 都道府県名,
    (
        select
            t_2015.TOTAL_AMT
        from
            t_2015
        where
            t_2015.PF_CODE = t.PF_CODE
    ) as 総人口2015年,
    (
        select
            t_2020.TOTAL_AMT
        from
            t_2020
        where
            t_2020.PF_CODE = t.PF_CODE
    )  as 総人口2020年,
    round(
        cast(
                (
                    select
                        t_2020.TOTAL_AMT
                    from
                        t_2020
                    where
                        t_2020.PF_CODE = t.PF_CODE
                )
        as real) / 
        cast(
                (
                    select
                        t_2015.TOTAL_AMT
                    from
                        t_2015
                    where
                        t_2015.PF_CODE = t.PF_CODE
                )
        as real) * 100
    )
        as 人口増加率
from POPU_TRANSITION t
    inner join PREFECTURE p
        on p.PF_CODE = t.PF_CODE
group by t.PF_CODE
order by 人口増加率 desc, p.PF_CODE;