select *, round(cast(総人口2020年 as real) / cast(総人口2015年 as real)*100,0) as 人口増加率
from(
select pf_code as 都道府県コード, pf_name as 都道府県名, max(popu2015) as 総人口2015年, max(popu2020) as 総人口2020年
from(
select pf_code, case survey_year when 2015 then total end as popu2015, case survey_year when 2020 then total end as popu2020
from(
select pf_code, survey_year, sum(total_amt) as total
from popu_transition
where survey_year in (2015,2020)
group by pf_code, survey_year
)) as popu
left join prefecture as pre
using(pf_code)
) order by 5 desc, 1