WITH nation_ranks 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.nation_code ) AS amt_rank
    , SUM(f.amt)OVER(PARTITION BY f.pf_code) AS total_num
FROM
    foreigner AS f
LEFT JOIN
    nationality AS n
ON
    f.nation_code = n.nation_code
LEFT 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 amt_rank = 1 THEN nation_name END) AS "1位 国名"
    , MAX(CASE WHEN amt_rank = 1 THEN amt END) AS "1位 人数"
    , MAX(CASE WHEN amt_rank = 2 THEN nation_name END) AS "2位 国名"
    , MAX(CASE WHEN amt_rank = 2 THEN amt END) AS "2位 人数"
    , MAX(CASE WHEN amt_rank = 3 THEN nation_name END) AS "3位 国名"
    , MAX(CASE WHEN amt_rank = 3 THEN amt END) AS "3位 人数"
FROM
    nation_ranks
GROUP BY
    pf_code
    , pf_name
ORDER BY
    max(total_num) DESC