select
    都道府県コード,
    都道府県名,
    "1nat" as "1位 国名",
    "1amt" as "1位 人数",
    "2nat" as "2位 国名",
    "2amt" as "2位 人数",
    "3nat" as "3位 国名",
    "3amt" as "3位 人数",
    wk5.sumnum as "合計人数"
from
    (select
        wk1.PF_CODE as "都道府県コード",
        wk2.PF_NAME as "都道府県名",
        max(case when wk1.num = 1 then NATION_NAME else null end) as "1nat",
        sum(case when wk1.num = 1 then wk1.amt else 0 end) as "1amt",
        max(case when wk1.num = 2 then NATION_NAME else null end) as "2nat",
        sum(case when wk1.num = 2 then wk1.amt else 0 end) as "2amt",
        max(case when wk1.num = 3 then NATION_NAME else null end) as "3nat",
        sum(case when wk1.num = 3 then wk1.amt else 0 end) as "3amt"
    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 and
            wk4.NATION_CODE != 113
        ) as wk1
    inner join
        PREFECTURE as wk2
    on
        wk1.PF_CODE = wk2.PF_CODE
    where
        num < 4
    group by
        wk1.PF_CODE
    ) as wk6
inner join
    (select
        PF_CODE,
        sum(AMT) as sumnum
    from
        FOREIGNER
    where
        NATION_CODE != 113
    group by
        PF_CODE
    ) as wk5
on
    wk6.都道府県コード = wk5.PF_CODE
order by
    合計人数 desc,
    都道府県コード desc
;