WITH PAST AS (
    SELECT SURVEY_YEAR, PF_CODE, TOTAL_AMT
    FROM POPU_TRANSITION
    WHERE SURVEY_YEAR = 2015
),
LATEST AS (
    SELECT SURVEY_YEAR, PF_CODE, TOTAL_AMT
    FROM POPU_TRANSITION
    WHERE SURVEY_YEAR = 2020
),
DELTA AS (
    SELECT PAST.PF_CODE,
        PAST.TOTAL_AMT AS 総人口2015年,
        LATEST.TOTAL_AMT AS 総人口2020年,
        ROUND(
            ((CAST(LATEST.TOTAL_AMT AS REAL) / CAST(PAST.TOTAL_AMT AS REAL)) * 100)
            , 0) AS 人口増加率
    FROM PAST
    LEFT OUTER JOIN LATEST
    ON PAST.PF_CODE = LATEST.PF_CODE
    WHERE LATEST.TOTAL_AMT > PAST.TOTAL_AMT
)

SELECT
    PREFECTURE.PF_CODE AS 都道府県コード,
    PREFECTURE.PF_NAME AS 都道府県名,
    DELTA.総人口2015年 AS 総人口2015年,
    DELTA.総人口2020年 AS 総人口2020年,
    DELTA.人口増加率 AS 人口増加率
FROM DELTA
LEFT OUTER JOIN PREFECTURE
ON DELTA.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    人口増加率 DESC,
    都道府県コード ASC
;