select 
    `都道府県コード`,
    `都道府県名`,
    '1位 国名',
    '1位 人数',
    '2位 国名',
    '2位 人数',
    '3位 国名',
    '3位 人数',
    xxxxx as '合計人数'
from (
    select 
        *,
        NTH_VALUE(NATION_NAME, 1) over w as '1位 国名',
        NTH_VALUE(sum_amt, 1) over w as '1位 人数',
        NTH_VALUE(NATION_NAME, 2) over w as '2位 国名',
        NTH_VALUE(sum_amt, 2) over w as '2位 人数',
        NTH_VALUE(NATION_NAME, 3) over w as '3位 国名',
        NTH_VALUE(sum_amt, 3) over w as '3位 人数',
        max(xxxxx) over (order by xxxxx desc) as xxxxx
    from (
        select 
            p.PF_CODE as  都道府県コード,
            p.PF_NAME as  都道府県名,
            n.NATION_CODE,
            max(n.NATION_NAME) as NATION_NAME,
            sum(f.AMT) as sum_amt,
            row_number() over (partition by p.PF_CODE order by sum(f.AMT) desc) as rank,
            sum(f.AMT) over (partition by p.PF_CODE) as xxxxx
        from FOREIGNER f
            join NATIONALITY n on n.NATION_CODE = f.NATION_CODE
            join PREFECTURE p on p.PF_CODE = f.PF_CODE
        where n.NATION_CODE != 113
        group by p.PF_CODE, n.NATION_CODE
        order by sum(f.AMT), p.PF_CODE, n.NATION_CODE
    )
    where rank < 4
    WINDOW w AS ( partition by `都道府県コード` order by sum_amt desc )
)
where rank = 3
order by xxxxx desc, `都道府県コード`