with tmp as (
select
    pre.pf_code as "都道府県コード"
    ,pre.pf_name as "都道府県名"
    ,sum(case when pop.survey_year = 2015 then pop.total_amt else 0 end) as "総人口2015年"
    ,sum(case when pop.survey_year = 2020 then pop.total_amt else 0 end) as "総人口2020年"
from
    popu_transition pop left join prefecture pre
    on pop.pf_code = pre.pf_code
group by
    pre.pf_code, pre.pf_name
)
select
    *
    ,round((cast("総人口2020年" as float) 
            / cast("総人口2015年" as float) * 100)) as "人口増減率"
from
    tmp
where
    round((cast("総人口2020年" as float) 
            / cast("総人口2015年" as float) * 100)) > 100
order by
    "人口増減率" desc, "都道府県コード"
;