with org as (
select
    pf_code
    , nation_code
    , sum(amt) as _1
from foreigner
where nation_code != '113'
group by pf_code, nation_code
), org2 as (
select
    *
    , rank() over (partition by pf_code order by _1 desc) as _2
    , sum(_1) over (partition by pf_code) as _3
from org
--) select * from org2;
), org3 as (
select
    *
from org2
where _2 < 4
-- ) select * from org3;
)
select
    a.pf_code as '都道府県コード'
    , b.pf_name as '都道府県名'
    , max(case when _2 = 1 then c.nation_name else '' end) as '1位 国名'
    , max(case when _2 = 1 then _1 else 0 end) as '1位 人数'
    , max(case when _2 = 2 then c.nation_name else '' end) as '2位 国名'
    , max(case when _2 = 2 then _1 else 0 end) as '2位 人数'
    , max(case when _2 = 3 then c.nation_name else '' end) as '3位 国名'
    , max(case when _2 = 3 then _1 else 0 end) as '3位 人数'
    , _3 as '合計人数'
from org3 as a
inner join prefecture as b
    on a.pf_code = b.pf_code
inner join nationality as c
    on a.nation_code = c.nation_code
group by 1
order by 9 desc, 1
;