with tmp1 as(
SELECT
pf.PF_CODE as 都道府県コード,
pf.PF_NAME as 都道府県名,
sum(case when SURVEY_YEAR = 2015 then TOTAL_AMT else 0 end) as 総人口2015年,
sum(case when SURVEY_YEAR = 2020 then TOTAL_AMT else 0 end) as 総人口2020年,
ROUND(cast(sum(case when SURVEY_YEAR = 2020 then TOTAL_AMT else 0 end) as real)/cast(sum(case when SURVEY_YEAR = 2015 then TOTAL_AMT else 0 end) as real)*100,0) as 人口増加率
FROM POPU_TRANSITION pp
INNER JOIN PREFECTURE pf ON pf.PF_CODE = pp.PF_CODE
GROUP BY 1,2
)
SELECT
*
FROM tmp1
WHERE 総人口2015年 < 総人口2020年
ORDER BY 人口増加率 desc, 都道府県コード asc