with
group_by_PF_NA as (
select
    PF_CODE
    , NATION_CODE
    , sum(AMT) as subtotal_AMT
from
    FOREIGNER
where
    NATION_CODE <> '113'
group by
    PF_CODE	, NATION_CODE
),

rank_na as (
select
    PF_CODE
    , NATION_CODE
    , subtotal_AMT
    , rank()over(PARTITION by PF_CODE order by subtotal_AMT desc, NATION_CODE) as rank_of_na
    , sum(subtotal_AMT)over(PARTITION by PF_CODE) as total_AMT
from
    group_by_PF_NA
),

join_mst  as (
select
    PF_CODE
    , PF_NAME
    , NATION_CODE
    , NATION_NAME
    , subtotal_AMT
    , rank_of_na
    , total_AMT
from
    rank_na
join 
    PREFECTURE
    USING(PF_CODE)
join 
    NATIONALITY
    USING(NATION_CODE)
where
    rank_of_na <= 3
)

select
    PF_CODE as 都道府県コード
    , max(PF_NAME) as 都道府県名
    , max(IIF(rank_of_na = 1, NATION_NAME, null)) as "1位 国名"
    , max(IIF(rank_of_na = 1, subtotal_AMT, null)) as "1位 人数"
    , max(IIF(rank_of_na = 2, NATION_NAME, null)) as "2位 国名"
    , max(IIF(rank_of_na = 2, subtotal_AMT, null)) as "2位 人数"
    , max(IIF(rank_of_na = 3, NATION_NAME, null)) as "3位 国名"
    , max(IIF(rank_of_na = 3, subtotal_AMT, null)) as "3位 人数"
    , max(total_AMT) as 合計人数
from
    join_mst
group by
    PF_CODE
order by 
    合計人数 desc, 都道府県コード