WITH POP_2015 AS (
    SELECT PF_CODE, TOTAL_AMT AS AMT_2015 FROM POPU_TRANSITION
    WHERE SURVEY_YEAR == 2015
), POP_2020 AS (
    SELECT PF_CODE, TOTAL_AMT AS AMT_2020 FROM POPU_TRANSITION
    WHERE SURVEY_YEAR == 2020
)


SELECT
    p.PF_CODE AS 都道府県コード,
    p.PF_NAME AS 都道府県名,
    POP_2015.AMT_2015 AS 総人口2015年,
    POP_2020.AMT_2020 AS 総人口2020年,
    POP_2020.AMT_2020 * 100 / POP_2015.AMT_2015 AS 人口増加率
FROM
    PREFECTURE AS p
    LEFT OUTER JOIN
    POP_2015
    ON
    p.PF_CODE == POP_2015.PF_CODE
    LEFT OUTER JOIN
    POP_2020
    ON
    p.PF_CODE == POP_2020.PF_CODE
WHERE
    人口増加率 > 100
ORDER BY
    人口増加率 DESC,
    都道府県コード ASC
;