WITH RANKING AS(
    SELECT
        PF_CODE,
        NATION_CODE,
        SUM(AMT) AS AMOUNT,
        RANK() OVER(PARTITION BY PF_CODE ORDER BY SUM(AMT) DESC) AS RANK
    FROM
        FOREIGNER
    WHERE
        NATION_CODE != '113'
    GROUP BY
        PF_CODE, NATION_CODE
)

SELECT
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    MAX(CASE WHEN RANK = 1 THEN NATION_NAME END) AS '1位 国名',
    MAX(CASE WHEN RANK = 1 THEN AMOUNT END) AS '1位 人数',
    MAX(CASE WHEN RANK = 2 THEN NATION_NAME END) AS '2位 国名',
    MAX(CASE WHEN RANK = 2 THEN AMOUNT END) AS '2位 人数',
    MAX(CASE WHEN RANK = 3 THEN NATION_NAME END) AS '3位 国名',
    MAX(CASE WHEN RANK = 3 THEN AMOUNT END) AS '3位 人数',
    SUM(AMOUNT) AS '合計人数'
FROM
    RANKING
INNER JOIN
    PREFECTURE USING(PF_CODE)
INNER JOIN
    NATIONALITY USING(NATION_CODE)
GROUP BY
    PF_CODE, PF_NAME
ORDER BY
    9 DESC, 1