with tab_1 as (
select
    pf_code
    , nation_code
    , amt
    , row_number() over(partition by pf_code order by amt, nation_code desc) as row_n
from
    foreigner
where
    nation_code != 113
)
, wholes as (
select
    pf_code
    , sum(amt) as sum_amt
from
    tab_1
group by
    pf_code
)
, tab_2 as (
select
    t1.pf_code as 都道府県コード
    , t2.pf_name as 都道府県名
    , max(case when row_n = 1 then t1.nation_code else null end) as "nation_1"
    , sum(case when row_n = 1 then amt else null end) as "1位 人数"
    , max(case when row_n = 2 then t1.nation_code else null end) as "nation_2"
    , sum(case when row_n = 2 then amt else null end) as "2位 人数"
    , max(case when row_n = 3 then t1.nation_code else null end) as "nation_3"
    , sum(case when row_n = 3 then amt else null end) as "3位 人数"
from
    tab_1 t1
left join
    prefecture t2
using(pf_code)
group by
    pf_code
)
select
    都道府県コード
    , 都道府県名
    , t3.nation_name as " 1位 国名"
    , "1位 人数"
    , t4.nation_name as "2位 国名"
    ,"2位 人数"
    , t5.nation_name as "3位 国名"
    , "3位 人数"
    , sum_amt as 合計人数
from
    tab_2 t1
left join
    NATIONALITY t3
on
    t1.nation_1 = t3.nation_code
left join
    NATIONALITY t4
on
    t1.nation_2 = t4.nation_code
left join
    NATIONALITY t5
on
    t1.nation_3 = t5.nation_code
left join
    wholes t6
on
    t1.都道府県コード = t6.pf_code
order by
    合計人数 desc
    , t1.都道府県コード asc