with tmp as (
select
    p.pf_code as "都道府県コード"
    ,p.pf_name as "都道府県名"
    ,n.nation_name
    ,n.nation_code
    ,sum(f.amt) over(partition by p.pf_code, p.pf_name) as "合計人数"
    ,sum(f.amt) over(partition by p.pf_code, p.pf_name, n.nation_code) "人数"
from
    foreigner f 
    left join prefecture p on f.pf_code = p.pf_code
    left join nationality n on f.nation_code = n.nation_code
where
    n.nation_code <> '113'
), tmp2 as (
select
    *
    ,row_number() over(partition by "都道府県コード" order by "人数" desc, nation_code asc) "ranking"
from
    tmp
order by
    "合計人数" desc, "人数" desc, "都道府県コード"
)
select
    distinct
    "都道府県コード"
    ,"都道府県名"
    ,max(case when "ranking" = 1 then nation_name else null end) over(partition by "都道府県コード") as "1位 国名"
    ,max(case when "ranking" = 1 then "人数" else 0 end) over(partition by "都道府県コード") as "1位 人数"
    ,max(case when "ranking" = 2 then nation_name else null end) over(partition by "都道府県コード") as "2位 国名"
    ,max(case when "ranking" = 2 then "人数" else 0 end) over(partition by "都道府県コード") as "2位 人数"
    ,max(case when "ranking" = 3 then nation_name else null end) over(partition by "都道府県コード") as "3位 国名"
    ,max(case when "ranking" = 3 then "人数" else 0 end) over(partition by "都道府県コード") as "3位 人数"
    ,"合計人数"
from
    tmp2
where
    "ranking" in (1, 2, 3)
order by
    "合計人数" desc, "人数" desc, "都道府県コード"
;