with foreigner_with_order as (
    select
        pf_code,
        nation_code,
        amt,
        row_number () over (
            partition by pf_code
            order by
                amt desc
        ) as amt_order
    from
        foreigner
),
merge_all as (
    select
        f.pf_code,
        p.pf_name,
        n.nation_name,
        f.amt,
        f.amt_order
    from
        foreigner_with_order as f
        inner join nationality as n on f.nation_code = n.nation_code
        inner join prefecture as p on f.pf_code = p.pf_code
    where n.nation_code <> '113'
)
select
    p.pf_code as "都道府県コード",
    p.pf_name as "都道府県名",
    (
        select
            m.nation_name
        from
            merge_all as m
        where
            p.pf_code = m.pf_code
            and m.amt_order = 1
    ) as "1位 国名",
    (
        select
            m.amt
        from
            merge_all as m
        where
            p.pf_code = m.pf_code
            and m.amt_order = 1
    ) as "1位 人数",
    (
        select
            m.nation_name
        from
            merge_all as m
        where
            p.pf_code = m.pf_code
            and m.amt_order = 2
    ) as "2位 国名",
    (
        select
            m.amt
        from
            merge_all as m
        where
            p.pf_code = m.pf_code
            and m.amt_order = 2
    ) as "2位 人数",
    (
        select
            m.nation_name
        from
            merge_all as m
        where
            p.pf_code = m.pf_code
            and m.amt_order = 3
    ) as "3位 国名",
    (
        select
            m.amt
        from
            merge_all as m
        where
            p.pf_code = m.pf_code
            and m.amt_order = 3
    ) as "3位 人数",
    (
        select
            sum(m.amt)
        from
            merge_all as m
        group by
            m.pf_code
        having
            p.pf_code = m.pf_code
    ) as "合計人数"
from
    prefecture as p
order by
    "合計人数" desc,
    "都道府県コード" asc;