with POPU_2015 as (
    select
        PF_CODE,
        cast(TOTAL_AMT as real) as TOTAL_AMT_2015
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2015
),
POPU_2020 as (
    select
        PF_CODE,
        cast(TOTAL_AMT as real) as TOTAL_AMT_2020
    from
        POPU_TRANSITION
    where
        SURVEY_YEAR = 2020
)
select
    PREFECTURE.PF_CODE as 都道府県コード,
    PREFECTURE.PF_NAME as 都道府県名,
    POPU_2015.TOTAL_AMT_2015 as 総人口2015年,
    POPU_2020.TOTAL_AMT_2020 as 総人口2020年,
    round (
        (
            POPU_2020.TOTAL_AMT_2020 / POPU_2015.TOTAL_AMT_2015
        ) * 100,
        -1
    ) as 人口増加率
from
    PREFECTURE
    join POPU_2015 on PREFECTURE.PF_CODE = POPU_2015.PF_CODE
    join POPU_2020 on PREFECTURE.PF_CODE = POPU_2020.PF_CODE
where
    POPU_2015.TOTAL_AMT_2015 < POPU_2020.TOTAL_AMT_2020
order by
    人口増加率 desc,
    都道府県コード;