with pop_2015 as (
    select SURVEY_YEAR,PF_CODE,TOTAL_AMT
        from POPU_TRANSITION
        where SURVEY_YEAR = 2015
), pop_2020 as (
    select SURVEY_YEAR,PF_CODE,TOTAL_AMT
        from POPU_TRANSITION
        where SURVEY_YEAR = 2020
)
select pop_2015.PF_CODE as 都道府県コード
      ,PREFECTURE.PF_NAME as 都道府県名
      ,pop_2015.TOTAL_AMT as 総人口2015年
      ,pop_2020.TOTAL_AMT as 総人口2020年
      ,round(cast(pop_2020.TOTAL_AMT as double) / cast(pop_2015.TOTAL_AMT as double) * 100) as 人口増加率
    from pop_2015
        inner join pop_2020
            on pop_2015.PF_CODE =pop_2020.PF_CODE
        left outer join PREFECTURE
            on pop_2015.PF_CODE = PREFECTURE.PF_CODE
    where cast(pop_2020.TOTAL_AMT as double) / cast(pop_2015.TOTAL_AMT as double) * 100 > cast(100 as double)
    order by 人口増加率 desc, 都道府県コード;