select
    tmp.pf_code as '都道府県コード',
    tmp.pf_name as '都道府県名',
    max(case tmp.survey_year when 2015 then tmp.total_amt end) as '総人口2015年',
    max(case tmp.survey_year when 2020 then tmp.total_amt end) as '総人口2020年',
    round(cast(max(case tmp.survey_year when 2020 then tmp.total_amt end) as real) / cast(max(case tmp.survey_year when 2015 then tmp.total_amt end) as real) * 100, 0) as '人口増加率'
from (
    select
      p.pf_code,
      pt.survey_year,
      p.pf_name,
      pt.total_amt
    from POPU_TRANSITION pt
    join PREFECTURE p on pt.pf_code = p.pf_code
) as tmp
group by tmp.pf_code
order by 5 desc, 1 asc;