select
    wk1.PF_CODE as "都道府県コード",
    wk2.PF_NAME as "都道府県名",
    sum(case when wk1.num = 1 then wk1.amt else 0 end) as "1位 人数",
    max(case when wk1.num = 1 then NATION_NAME else null end) as "1位 国名",
    sum(case when wk1.num = 2 then wk1.amt else 0 end) as "2位 人数",
    max(case when wk1.num = 2 then NATION_NAME else null end) as "2位 国名",
    sum(case when wk1.num = 3 then wk1.amt else 0 end) as "3位 人数",
    max(case when wk1.num = 3 then NATION_NAME else null end) as "3位 国名",
    sum(wk1.amt) as "合計人数"
from
    (select
        wk4.PF_CODE,
        wk4.NATION_CODE,
        wk4.AMT,
        wk3.NATION_NAME,
        row_number() over(partition by wk4.PF_CODE order by AMT desc,wk4.NATION_CODE asc) as num
    from
        FOREIGNER as wk4
    inner join
        NATIONALITY as wk3
    on 
        wk3.NATION_CODE = wk4.NATION_CODE
    ) as wk1
inner join
    PREFECTURE as wk2
on
    wk1.PF_CODE = wk2.PF_CODE
where
    num < 4
group by
    wk1.PF_CODE
order by
    合計人数 desc,
    都道府県コード desc
;