with
pop_2015 as (
select
    PF_CODE
    , TOTAL_AMT as TOTAL_AMT_2015
from
    POPU_TRANSITION
where
    SURVEY_YEAR = 2015
),

pop_2020 as (
select
    PF_CODE
    , TOTAL_AMT as TOTAL_AMT_2020
from
    POPU_TRANSITION
where
    SURVEY_YEAR = 2020
),

compare_pop as (
select
    PF_CODE
    , TOTAL_AMT_2015
    , TOTAL_AMT_2020
    , ROUND(
        cast(TOTAL_AMT_2020 as REAL) / cast(TOTAL_AMT_2015 as REAL) * 100
        , 1
    ) as appreciation_rete
from
    pop_2015
join
    pop_2020
    using(PF_CODE)
where
    TOTAL_AMT_2015 < TOTAL_AMT_2020
)

select
    PF_CODE as 都道府県コード
    , PF_NAME as 都道府県名
    , TOTAL_AMT_2015 as 総人口2015年
    , TOTAL_AMT_2020  as 総人口2020年
    , appreciation_rete as 人口増加率
from
    compare_pop
join
    PREFECTURE 
    using(PF_CODE)
order by
    人口増加率 desc, 都道府県コード