with TOTAL as (
    select
        PF_CODE
        , sum(AMT) as TOTAL
    from FOREIGNER
    where NATION_CODE <> 113
    group by PF_CODE
), TMP as (
    select
        F1.PF_CODE
        , PF_NAME
        , NATION_CODE
        , NATION_NAME as FIRST
        , AMT as FIRST_AMT
        , max(NATION_NAME) 
            over(partition by PF_CODE  order by AMT desc, F1.NATION_CODE asc
                    rows between 1 following and 1 following) as SECOND
        , max(AMT) 
            over(partition by PF_CODE order by AMT desc, F1.NATION_CODE asc
                    rows between 1 following and 1 following) as SECOND_AMT
        , max(NATION_NAME) 
            over(partition by PF_CODE order by AMT desc, F1.NATION_CODE asc 
                rows between 2 following and 2 following) as THIRD
        , max(AMT) 
            over(partition by PF_CODE order by AMT desc, F1.NATION_CODE asc 
                rows between 2 following and 2 following) as THIRD_AMT
    from FOREIGNER as F1
    inner join NATIONALITY using(NATION_CODE)
    inner join PREFECTURE using(PF_CODE)
    where NATION_CODE <> 113
)

select
    TMP.PF_CODE as "都道府県コード"
    , PF_NAME as "都道府県名"
    , FIRST as "1位 国名"
    , FIRST_AMT as "1位 人数"
    , SECOND as "2位 国名"
    , SECOND_AMT as "2位 人数"
    , THIRD as "3位 国名"
    , THIRD_AMT as "3位 人数"
    , TOTAL.TOTAL as "合計人数"
from TMP
inner join TOTAL using(PF_CODE)
where NATION_CODE in 
    (select NATION_CODE from FOREIGNER 
    where TMP.PF_CODE = PF_CODE  and NATION_CODE <> 113
    order by AMT desc, NATION_CODE limit 1)
order by "合計人数" desc, TMP.PF_CODE asc;