with T as (
    select
    	P.PF_CODE,
    	P.PF_NAME,
    	N.NATION_CODE,
    	N.NATION_NAME,
    	sum(F.AMT) as AMT
    from
    	PREFECTURE P
    inner join
    	FOREIGNER F
    on 
    	P.PF_CODE = F.PF_CODE
    inner join
    	NATIONALITY N
    on
    	F.NATION_CODE = N.NATION_CODE
    group by
    	P.PF_CODE,
    	N.NATION_CODE
)

select
R1.P1 as 都道府県コード,
R1.PN1 as 都道府県名,
R1.NN1 as "1位 国名",
R1.A1 as "2位 人数",
R2.NN2 as "1位 国名",
R2.A2 as "2位 人数",
R3.NN3 as "2位 国名",
R3.A3 as "3位 人数",
F.AMT as 合計人数
from
(
select
    PF_CODE as P1,
    PF_NAME as PN1,
    NATION_CODE as N1,
    NATION_NAME as NN1,
    sum(AMT) as A1
from
(
select
    PF_CODE,
    PF_NAME,
    rank() over(PARTITION BY PF_CODE order by AMT desc) as ranking,
    NATION_CODE,
    NATION_NAME,
    AMT
from
    T
where 
    NATION_CODE <> '113'
group by
    PF_CODE,
    NATION_CODE
) A
where ranking = 1
group by
    PF_CODE,
    NATION_CODE
) R1
inner join
(
select
    PF_CODE as P2,
    PF_NAME as PN2,
    NATION_CODE as N2,
    NATION_NAME as NN2,
    sum(AMT) as A2
from
(
select
    PF_CODE,
    PF_NAME,
    rank() over(PARTITION BY PF_CODE order by AMT desc) as ranking,
    NATION_CODE,
    NATION_NAME,
    AMT
from
    T
where 
    NATION_CODE <> '113'
group by
    PF_CODE,
    NATION_CODE
) A
where ranking = 2
group by
    PF_CODE,
    NATION_CODE
) R2
on
R1.P1 = R2.P2
inner join
(
select
    PF_CODE as P3,
    PF_NAME as PN3,
    NATION_CODE as N3,
    NATION_NAME as NN3,
    sum(AMT) as A3
from
(
select
    PF_CODE,
    PF_NAME,
    rank() over(PARTITION BY PF_CODE order by AMT desc) as ranking,
    NATION_CODE,
    NATION_NAME,
    AMT
from
    T
where 
    NATION_CODE <> '113'
group by
    PF_CODE,
    NATION_CODE
) A
where ranking = 3
group by
    PF_CODE,
    NATION_CODE
) R3
on
R2.P2 = R3.P3
inner join
    FOREIGNER F
on
    R3.P3 = F.PF_CODE
    and
    F.NATION_CODE <> '113'
group by R3.P3