WITH d as (
SELECT
	a.PF_CODE,
	rank() over(PARTITION BY a.PF_CODE ORDER BY a.AMT DESC ,a.NATION_CODE ASC ) as RANK ,
	a.NATION_CODE,
	b.NATION_NAME,
	a.AMT
FROM 
	(SELECT * FROM FOREIGNER WHERE NATION_CODE != "113") as a
inner  join 
	NATIONALITY as b 
on
	a.NATION_CODE = b.NATION_CODE
)
,d_sum as (
SELECT
	PF_CODE,
	sum(AMT) as sum_AMT
FROM 
	FOREIGNER
GROUP BY
	AMT
)

,
d_rank as (
SELECT
	d12.PF_CODE,
	NATION_NAME_1,
	AMT_1,
	NATION_NAME_2,
	AMT_2,
	d3.NATION_NAME as NATION_NAME_3,
	d3.AMT as AMT_3
FROM
	(SELECT 
		d1.PF_CODE,
		d1.NATION_NAME as NATION_NAME_1,
		d1.AMT as AMT_1,
		d2.NATION_NAME as NATION_NAME_2,
		d2.AMT as AMT_2
	FROM 
		(SELECT * FROM d where RANK = 1) as d1
	inner join 
		(SELECT * FROM d where RANK = 2) as d2
	ON
		d1.PF_CODE = d2.PF_CODE
	) as d12
inner join
	(SELECT * FROM d where RANK = 3) as d3
ON
	d12.PF_CODE = d3.PF_CODE
)

SELECT 
	c.PF_CODE as 都道府県コード,
	d.PF_NAME as 都道府県名	,
	c.NATION_NAME_1 as "1位 国名",
	c.AMT_1 as "1位 人数",
	c.NATION_NAME_2 as "2位 国名",
	c.AMT_2 as "2位 人数",
	c.NATION_NAME_3 as "3位 国名",
	c.AMT_3 as "3位 人数",
	c.sum_AMT as 合計人数
FROM 
	(SELECT 
		d_rank.*,
		d_sum.sum_AMT
	FROM 
		d_rank 
	INNER JOIN 
		d_sum
	ON
		d_rank.PF_CODE = d_sum.PF_CODE
	) as c 
left join
	PREFECTURE as d
on 
	c.PF_CODE = d.PF_CODE
ORDER BY 
	合計人数 DESC ,都道府県コード ASC 
;