WITH POPU_2015 AS (
    select SURVEY_YEAR, PF_CODE, TOTAL_AMT, MALE_AMT, FEMALE_AMT
      from POPU_TRANSITION
      where SURVEY_YEAR=2015
), POPU_2020 AS (
    select SURVEY_YEAR, PF_CODE, TOTAL_AMT, MALE_AMT, FEMALE_AMT
      from POPU_TRANSITION
      where SURVEY_YEAR=2020
), POPU_SABUN AS (
    select 
        POPU_2020.PF_CODE, 
        POPU_2015.TOTAL_AMT as AMT_2015, 
        POPU_2020.TOTAL_AMT as AMT_2020, 
        POPU_2020.TOTAL_AMT-POPU_2015.TOTAL_AMT as AMT_SABUN 
      from POPU_2020 INNER JOIN POPU_2015 ON POPU_2020.PF_CODE=POPU_2015.PF_CODE
)
select 
    SABUN.PF_CODE as '都道府県コード', 
    PRE.PF_NAME as '都道府県名', 
    SABUN.AMT_2015 as '総人口2015年', 
    SABUN.AMT_2020 as '総人口2020年', 
    100+ROUND(CAST(SABUN.AMT_SABUN as REAL)/SABUN.AMT_2015*100, 0) as '人口増加率'
from POPU_SABUN SABUN INNER JOIN PREFECTURE PRE ON SABUN.PF_CODE=PRE.PF_CODE
WHERE AMT_SABUN>0
order by 5 desc, 1 asc
;