WITH temp AS (
    SELECT
        foreigner.pf_code 
        , prefecture.pf_name
        , nationality.nation_name
        , amt
        , ROW_NUMBER() OVER(
            PARTITION BY foreigner.pf_code ORDER BY amt DESC, nationality.nation_code DESC) AS rank_
    FROM
        foreigner
        INNER JOIN
            prefecture
            ON foreigner.pf_code = prefecture.pf_code
        INNER JOIN
            nationality
            ON foreigner.nation_code = nationality.nation_code
    WHERE
        foreigner.nation_code != '113'
)
SELECT
    pf_code AS 都道府県コード
    , pf_name AS 都道府県名
    , MAX(CASE WHEN rank_ == 1 THEN nation_name END) AS "1位 国名"
    , MAX(CASE WHEN rank_ == 1 THEN amt END) AS "1位 人数"
    , MAX(CASE WHEN rank_ == 2 THEN nation_name END) AS "2位 国名"
    , MAX(CASE WHEN rank_ == 2 THEN amt END) AS "2位 人数"
    , MAX(CASE WHEN rank_ == 3 THEN nation_name END) AS "3位 国名"
    , MAX(CASE WHEN rank_ == 3 THEN amt END) AS "3位 人数"
    , SUM(amt) AS 合計人数
FROM
    temp
GROUP BY
    1, 2
ORDER BY
    9 DESC, 1 ASC