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

,PF_CODE AS(
    SELECT DISTINCT PF_CODE, 1 AS RANK_1, 2 AS RANK_2, 3 AS RANK_3 FROM FOREIGNER
)

,RANK1 AS(
    SELECT
        p.PF_CODE
        ,p.RANK_1
        ,p.RANK_2
        ,p.RANK_3
        ,t.NATION_CODE AS "1位 国名"
        ,t.AMT AS "1位 人数"
    FROM PF_CODE p JOIN TMP t on p.PF_CODE = t.PF_CODE AND p.RANK_1 = t.RANK 
)

,RANK2 AS(
    SELECT
        p.PF_CODE
        ,p.RANK_1
        ,p.RANK_2
        ,p.RANK_3
        ,p."1位 国名"
        ,p."1位 人数"
        ,t.NATION_CODE AS "2位 国名"
        ,t.AMT AS "2位 人数"
    FROM RANK1 p JOIN TMP t on p.PF_CODE = t.PF_CODE AND p.RANK_2 = t.RANK 
)

,RANK3 AS(
    SELECT 
        p.PF_CODE
        ,p.RANK_1
        ,p.RANK_2
        ,p.RANK_3
        ,p."1位 国名"
        ,p."1位 人数"
        ,p."2位 国名"
        ,p."2位 人数"
        ,t.NATION_CODE AS "3位 国名"
        ,t.AMT AS "3位 人数"
    FROM RANK2 p JOIN TMP t on p.PF_CODE = t.PF_CODE AND p.RANK_3 = t.RANK 
)

SELECT
    *
FROM RANK3