select popu.pf_code as 都道府県コード
, pre.pf_name as 都道府県名
, popu.popu2015 as 総人口2015年
, popu.popu2020 as 総人口2020年
, round(cast(popu2020 as real) / cast(popu2015 as real)*100,0) as 人口増加率
from(
select pf_code, max(popu2015) as popu2015, max(popu2020) as popu2020
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
) 
) group by pf_code
having max(popu2020) > max(popu2015)
) as popu
left join prefecture as pre
on popu.pf_code = pre.pf_code
order by 5 desc, 1