WITH FOREIGNER_TBL AS(
    SELECT
        PF_CODE
        ,NATION_CODE
        ,AMT
        ,RANK() OVER(
            PARTITION BY
                PF_CODE
            ORDER BY
                AMT DESC
                ,NATION_CODE
        ) AS RANK
    FROM
        FOREIGNER
    WHERE
        NATION_CODE != "113"
)
SELECT
    P.PF_CODE
    ,P.PF_NAME
    ,MAX(CASE F.RANK WHEN 1 THEN N.NATION_NAME ELSE NULL END)
    ,MAX(CASE F.RANK WHEN 1 THEN AMT ELSE 0 END)
FROM
    FOREIGNER_TBL AS F
        INNER JOIN PREFECTURE AS P
            ON F.PF_CODE = P.PF_CODE
        INNER JOIN NATIONALITY AS N
            ON F.NATION_CODE = N.NATION_CODE
GROUP BY
    P.PF_CODE
    ,P.PF_NAME