SELECT
    pf_code AS 都道府県コード,
    pf_name AS 都道府県名,
    MAX(CASE sub.rank WHEN 1 THEN nation_name ELSE NULL END) AS "1位 国名",
    MAX(CASE sub.rank WHEN 1 THEN amt ELSE 0 END) AS "1位 人数",
    MAX(CASE sub.rank WHEN 2 THEN nation_name ELSE NULL END) AS "2位 国名",
    MAX(CASE sub.rank WHEN 2 THEN amt ELSE 0 END) AS "2位 人数",
    MAX(CASE sub.rank WHEN 3 THEN nation_name ELSE NULL END) AS "3位 国名",
    MAX(CASE sub.rank WHEN 3 THEN amt ELSE 0 END) AS "3位 人数",
    SUM(amt) AS 合計人数
FROM
    (
        SELECT
            f.pf_code AS pf_code,
            p.pf_name AS pf_name,
            n.nation_name AS nation_name,
            f.amt AS amt,
            RANK() OVER (
                PARTITION BY
                    f.pf_code
                ORDER BY
                    amt DESC,
                    f.nation_code
            ) 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'
    ) AS sub
GROUP BY
    sub.pf_code,
    sub.pf_name
ORDER BY
    合計人数 DESC,
    都道府県コード ASC;