with base_year as (
select
  pf_code as  pf_code
  , total_amt as total_amt
from
  popu_transition
where
  survey_year = 2015
), compare_year as (
select
  pf_code as  pf_code
  , total_amt as total_amt
from
  popu_transition
where
  survey_year = 2020
), comparison as (
select
  t1.pf_code as pf_code
  , t1.total_amt as p_total
  , t2.total_amt as c_total
from
  base_year t1
inner join
  compare_year t2
on t1.pf_code = t2.pf_code
)
select
  t1.pf_code as '都道府県コード'
  , t2.pf_name as '都道府県名'
  , t1.p_total as '総人口2015年'
  , t1.c_total as '総人口2020年'
  , round(cast(t1.c_total as real) / cast(t1.p_total as real) * 100) as '人口増加率'
from
  comparison t1
inner join
  prefecture t2
on t1.pf_code = t2.pf_code
where
  round(cast(t1.c_total as real) / t1.p_total * 100) >= 100
  and t1.c_total >= t1.p_total
order by
  5 desc
  , 1 asc
;