WITH ranked_data AS (
    SELECT
        p.PF_CODE AS 都道府県コード,
        p.PF_NAME AS 都道府県名,
        n.NATION_NAME,
        f.AMT,
        RANK() OVER (PARTITION BY p.PF_CODE ORDER BY f.AMT DESC, f.NATION_CODE) AS ランク,
        SUM(f.AMT) OVER (PARTITION BY p.PF_CODE) AS 合計人数
    FROM
        FOREIGNER f
    INNER JOIN
        NATIONALITY n ON f.NATION_CODE = n.NATION_CODE
    INNER JOIN
        PREFECTURE p ON f.PF_CODE = p.PF_CODE
    WHERE
        f.NATION_CODE != 113
)
SELECT
    都道府県コード
    ,都道府県名
    ,MAX(CASE WHEN ランク = 1 THEN NATION_NAME END) AS "1位 国名"
    ,MAX(CASE WHEN ランク = 1 THEN AMT END) AS "1位 人数"
    ,MAX(CASE WHEN ランク = 2 THEN NATION_NAME END) AS "2位 国名"
    ,MAX(CASE WHEN ランク = 2 THEN AMT END) AS "2位 人数"
    ,MAX(CASE WHEN ランク = 3 THEN NATION_NAME END) AS "3位 国名"
    ,MAX(CASE WHEN ランク = 3 THEN AMT END) AS "3位 人数"
    ,合計人数
FROM
    ranked_data
GROUP BY
    都道府県コード, 都道府県名, 合計人数
ORDER BY
    合計人数 DESC,
    都道府県コード;