with
    f1 as (
        select
            *
        from
            foreigner
        where
            nation_code <> 113
        order by
            pf_code, amt desc
    )
    ,f2 as (
        select
            *
        from
            foreigner
        where
            nation_code <> 113
        order by
            pf_code, amt desc
    )
    ,fj as (
        select
            f1.pf_code as f1_pf_code
            ,f1.nation_code as f1_nation_code
            ,f1.amt as f1_amt
            ,f2.pf_code as f2_pf_code
            ,f2.nation_code as f2_nation_code
            ,f2.amt as f2_amt
        from
            f1
        left join
            f2
        on
            f1.pf_code = f2.pf_code
            and f1.amt <= f2.amt
        order by
            1,2
    )
    ,fr as (
        select
            fj.f1_pf_code as pf_code
            ,fj.f1_nation_code as nation_code
            ,fj.f1_amt as amt
            ,count(f2_nation_code) as ranking
        from
            fj
        group by
            1, 2
        order by
            1, 4, 2
    )
    ,frr as (
        select
            pf_code
            ,nation_code
            ,amt
            ,row_number() over(partition by pf_code) as ranking
        from
            fr
        )
   ,f_1 as (
        select
            *
        from
            frr
        join
            nationality n
        on
            fr.nation_code = n.nation_code
        where
            ranking = 1
        order by
            1, 3
    )
    ,f_2 as (
        select
            *
        from
            frr
        join
            nationality n
        on
            fr.nation_code = n.nation_code
        where
            ranking = 2
        order by
            1, 3
    )
    ,f_3 as (
        select
            *
        from
            frr
        join
            nationality n
        on
            fr.nation_code = n.nation_code
        where
            ranking = 3
        order by
            1, 3
    )
    ,f_rank as (
        select
            f_1.pf_code
            ,f_1.nation_name as nation_name_1
            ,f_1.amt as amt_1
            ,f_2.nation_name as nation_name_2
            ,f_2.amt as amt_2
            ,f_3.nation_name as nation_name_3
            ,f_3.amt as amt_3
        from
            f_1
        join
            f_2
        on
            f_1.pf_code = f_2.pf_code
        join
            f_3
        on
            f_1.pf_code = f_3.pf_code
        order by
            1
    )
    ,f_sum as (
        select
            pf_code
            ,sum(amt) as amt
        from
            foreigner
        where
            nation_code <> 113
        group by
            1
    )
select
    p.pf_code as '都道府県コード'
    ,p.pf_name as '都道府県名'
    ,f_rank.nation_name_1 as '1位 国名'
    ,f_rank.amt_1 as '1位 人数'
    ,f_rank.nation_name_2 as '2位 国名'
    ,f_rank.amt_2 as '2位 人数'
    ,f_rank.nation_name_3 as '3位 国名'
    ,f_rank.amt_3 as '3位 人数'
    ,f_sum.amt as '合計人数'
from
    f_rank
join
    prefecture p
on
    f_rank.pf_code = p.pf_code
join
    f_sum
on
    f_rank.pf_code = f_sum.pf_code
order by
    9 desc, 1