SELECT SUB.PF_CODE AS "都道府県コード", SUB.PF_NAME AS "都道府県名",
MAX(CASE WHEN SUB.RANK = 1 THEN SUB.NATION_NAME ELSE NULL END) AS "1位 国名",
MAX(CASE WHEN SUB.RANK = 1 THEN SUB.AMT ELSE NULL END) AS "1位 人数",
MAX(CASE WHEN SUB.RANK = 2 THEN SUB.NATION_NAME ELSE NULL END) AS "2位 国名",
MAX(CASE WHEN SUB.RANK = 2 THEN SUB.AMT ELSE NULL END) AS "2位 人数",
MAX(CASE WHEN SUB.RANK = 3 THEN SUB.NATION_NAME ELSE NULL END) AS "3位 国名",
MAX(CASE WHEN SUB.RANK = 3 THEN SUB.AMT ELSE NULL END) AS "3位 人数",
SUM(SUB.AMT) AS "合計人数"
FROM (
    SELECT P.PF_CODE AS PF_CODE, P.PF_NAME AS PF_NAME, N.NATION_NAME AS NATION_NAME, F.AMT AS AMT,
    RANK() OVER(PARTITION BY P.PF_CODE ORDER BY F.AMT DESC) AS RANK
    FROM FOREIGNER AS F JOIN NATIONALITY AS N USING(NATION_CODE)
    JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE N.NATION_CODE != "113"
) AS SUB
GROUP BY SUB.PF_CODE
ORDER BY "合計人数" DESC, SUB.PF_CODE;