SELECT
    pf.pf_code AS 都道府県コード,
    pf.pf_name AS 都道府県名,
    MAX(
        CASE tran.survey_year
        WHEN 2015 THEN tran.total_amt
        ELSE NULL
        END
    ) AS 総人口2015年,
    MAX(
        CASE tran.survey_year
        WHEN 2020 THEN tran.total_amt
        ELSE NULL
        END
    ) AS 総人口2020年,
    ROUND(
        MAX(
            CASE tran.survey_year
            WHEN 2020 THEN tran.total_amt
            ELSE NULL
            END
        ) * 100.0 / MAX(
            CASE tran.survey_year
            WHEN 2015 THEN tran.total_amt
            ELSE NULL
            END
        )
    ) AS 人口増加率
FROM popu_transition AS tran
JOIN prefecture AS pf
ON tran.pf_code = pf.pf_code
WHERE tran.survey_year = 2015 OR tran.survey_year = 2020
GROUP BY tran.pf_code
HAVING
    MAX(
        CASE tran.survey_year
        WHEN 2015 THEN tran.total_amt
        ELSE NULL
        END
    ) < MAX(
        CASE tran.survey_year
        WHEN 2020 THEN tran.total_amt
        ELSE NULL
        END
    )
ORDER BY 人口増加率 DESC, tran.pf_code;