with popu_amount as (
    select
        p.pf_code,
        p.pf_name,
        pt.total_amt,
        pt.survey_year
    from
        prefecture as p
    inner join
        popu_transition as pt
    on
        p.pf_code = pt.pf_code
    where
        pt.survey_year = 2015
    or
        pt.survey_year = 2020
),
popu_rate as (
    select
        pf_code,
        round(
            cast( 
                (
                    select total_amt
                    from popu_amount
                    where survey_year = 2015
                )
            as real)
            /
            (
                select total_amt
                from popu_amount
                where survey_year = 2020
            )
            *
            100,
            0
        ) as rate
    from
        prefecture
)
select
    p.pf_code as 都道府県コード,
    p.pf_name as 都道府県名,
    (
        select pa.total_amt
        from popu_amount as pa
        where 
            p.pf_code = pa.pf_code
        and
            pa.survey_year = 2015
    ) as 総人口2015年,
    (
        select pa.total_amt
        from popu_amount as pa
        where 
            p.pf_code = pa.pf_code
        and
            pa.survey_year = 2020
    ) as 総人口2020年,
    (
        select pr.rate
        from popu_rate as pr
        where p.pf_code = pr.pf_code
    ) as 人口増加率
from
    prefecture as p
order by
    人口増加率 desc,
    都道府県コード asc;