WITH POPU_2015 AS (
    SELECT *
    FROM `POPU_TRANSITION`
    WHERE SURVEY_YEAR = '2015'
)
, POPU_2020 AS (
    SELECT *
    FROM `POPU_TRANSITION`
    WHERE SURVEY_YEAR = '2020'
)

SELECT
    p.PF_CODE AS '都道府県コード',
    p.PF_NAME AS '都道府県名',
    p15.TOTAL_AMT AS '総人口2015年',
    p20.TOTAL_AMT AS '総人口2020年',
    ROUND((CAST(p20.TOTAL_AMT AS REAL) / p15.TOTAL_AMT)*100) AS '人口増加率'
FROM
    `POPU_2015` AS p15
    INNER JOIN `POPU_2020` AS p20
    ON p15.PF_CODE = p20.PF_CODE
    INNER JOIN `PREFECTURE` AS p
    ON p20.PF_CODE = p.PF_CODE
WHERE
    p15.TOTAL_AMT < p20.TOTAL_AMT
ORDER BY
    ROUND((CAST(p20.TOTAL_AMT AS REAL) / p15.TOTAL_AMT)*100) DESC,
    p.PF_CODE
;