with tg_nationality as (
select
    NATION_CODE
    ,NATION_NAME
from
    NATIONALITY
where
    NATION_CODE not in ("113")
)
,
join_code as (
select
    a.PF_CODE
    ,PF_NAME
    ,a.NATION_CODE
    ,NATION_NAME
    ,AMT
    ,ROW_NUMBER() OVER(PARTITION BY PF_NAME ORDER BY AMT desc, a.NATION_CODE asc) as amt_rank
from
    FOREIGNER a
inner join
    tg_nationality b
on
    a.NATION_CODE = b.NATION_CODE
inner join
    PREFECTURE c
on
    a.PF_CODE = c.PF_CODE
)
,
agg_total_amt as (
select
    PF_CODE
    ,PF_NAME
    ,sum(AMT) as "合計人数"
from
    join_code
group by
    PF_CODE
    ,PF_NAME
)
,
extract_top3 as (
select
    PF_CODE
    ,PF_NAME
    ,max(case when amt_rank = 1 then NATION_NAME else null end) as "1位 国名"
    ,sum(case when amt_rank = 1 then AMT else 0 end) as "1位 人数"
    ,max(case when amt_rank = 2 then NATION_NAME else null end) as "2位 国名"
    ,sum(case when amt_rank = 2 then AMT else 0 end) as "2位 人数"
    ,max(case when amt_rank = 3 then NATION_NAME else null end) as "3位 国名"
    ,sum(case when amt_rank = 3 then AMT else 0 end) as "3位 人数"
from
    join_code
where
    amt_rank <= 3
group by
    PF_CODE
    ,PF_NAME
)

select
    a.PF_CODE as "都道府県コード"
    ,a.PF_NAME as "都道府県名"
    ,"1位 国名"
    ,"1位 人数"
    ,"2位 国名"
    ,"2位 人数"
    ,"3位 国名"
    ,"3位 人数"
    ,"合計人数"
from
    extract_top3 a
inner join
    agg_total_amt b
on
    a.PF_CODE = b.PF_CODE
    and a.PF_NAME = b.PF_NAME
order by
    "合計人数" desc
    ,"都道府県コード" asc