WITH POPU_TRANSITION_2015 AS (
    SELECT
        *
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2015
),

POPU_TRANSITION_2020 AS (
    SELECT
        *
    FROM
        POPU_TRANSITION
    WHERE
        SURVEY_YEAR = 2020
)

SELECT
    P.PF_CODE AS 都道府県コード,
    P.PF_NAME AS 都道府県名,
    P_T_2015.TOTAL_AMT AS 総人口2015,
    P_T_2020.TOTAL_AMT AS 総人口2020,
    ROUND(((CAST(P_T_2020.TOTAL_AMT AS REAL) - CAST(P_T_2015.TOTAL_AMT AS REAL)) / CAST(P_T_2015.TOTAL_AMT AS REAL) * 100) + 100) AS 人口増加率
FROM
    POPU_TRANSITION_2015 P_T_2015
    INNER JOIN POPU_TRANSITION_2020 P_T_2020
        ON P_T_2020.PF_CODE = P_T_2015.PF_CODE
    INNER JOIN PREFECTURE P
        ON P.PF_CODE = P_T_2015.PF_CODE
WHERE
    P_T_2020.TOTAL_AMT > P_T_2015.TOTAL_AMT
ORDER BY
    人口増加率 DESC,
    都道府県コード ASC
;