WITH RANKING AS(
SELECT
    PF_CODE
    ,PF_NAME
    ,NATION_CODE
    ,NATION_NAME
    ,AMT
    ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS RANK
FROM FOREIGNER NATURAL JOIN NATIONALITY NATURAL JOIN PREFECTURE
WHERE NATION_CODE != '113'
)

SELECT
    PF_CODE AS "都道府県コード"
    ,PF_NAME AS "都道府県名"
    ,MAX(CASE RANK WHEN 1 THEN NATION_NAME END) AS "1位 国名"
    ,MAX(CASE RANK WHEN 1 THEN AMT END) AS "1位 人数"
    ,MAX(CASE RANK WHEN 2 THEN NATION_NAME END) AS "2位 国名"
    ,MAX(CASE RANK WHEN 2 THEN AMT END) AS "2位 人数"
    ,MAX(CASE RANK WHEN 3 THEN NATION_NAME END) AS "3位 国名"
    ,MAX(CASE RANK WHEN 3 THEN AMT END) AS "3位 人数"
    ,SUM(AMT) AS "合計人数"
FROM RANKING
GROUP BY
    PF_CODE
    ,PF_NAME
ORDER BY
    SUM(AMT) DESC
    ,PF_CODE