WITH
fff AS (
SELECT
	PF_CODE,
	NATION_CODE,
	SUM(AMT) AS SUM_AMT
FROM
	FOREIGNER
WHERE
	NATION_CODE <> "113"
GROUP BY
PF_CODE, NATION_CODE
)

, tmp AS (
SELECT
fff.PF_CODE,
fff.SUM_AMT,
RANK() OVER (PARTITION BY fff.PF_CODE ORDER BY fff.SUM_AMT DESC, fff.NATION_CODE) AS rk,
	n.NATION_NAME,
	p.PF_NAME
FROM
fff
LEFT JOIN
	NATIONALITY n
ON
	fff.NATION_CODE = n.NATION_CODE
LEFT JOIN
	PREFECTURE p
ON
	fff.PF_CODE = p.PF_CODE
)




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