WITH joined AS (
    SELECT
        f.pf_code,
        p.pf_name,
        n.nation_name,
        f.amt,
        rank() OVER(
            PARTITION by f.pf_code
            ORDER BY
                f.amt DESC,
                f.pf_code ASC
        ) AS rank
    FROM
        foreigner AS f
        INNER JOIN nationality AS n ON f.nation_code = n.nation_code
        INNER JOIN prefecture AS p ON f.pf_code = p.pf_code
    WHERE
        f.nation_code != '113'
)
SELECT
    pf_code AS '都道府県コード',
    pf_name AS '都道府県名',
    max(
        CASE
            WHEN rank = 1 THEN nation_name
            ELSE NULL
        END
    ) AS '1位 国名',
    max(
        CASE
            WHEN rank = 1 THEN amt
            ELSE NULL
        END
    ) AS '1位 人数',
    max(
        CASE
            WHEN rank = 2 THEN nation_name
            ELSE NULL
        END
    ) AS '2位 国名',
    max(
        CASE
            WHEN rank = 2 THEN amt
            ELSE NULL
        END
    ) AS '2位 人数',
    max(
        CASE
            WHEN rank = 3 THEN nation_name
            ELSE NULL
        END
    ) AS '3位 国名',
    max(
        CASE
            WHEN rank = 3 THEN amt
            ELSE NULL
        END
    ) AS '3位 人数',
    sum(amt) AS '合計人数'
FROM
    joined
GROUP BY
    pf_code
ORDER BY
    `合計人数` DESC,
    pf_code ASC