select
      "都道府県コード"
    , "都道府県名"
    , "1位 国名"
    , "1位 人数"
    , "2位 国名"
    , "2位 人数"
    , "3位 国名"
    , "4位 人数"
    , b.AMT as "合計人数"
from
    (
    select
          PF_CODE as "都道府県コード"
        , PF_NAME as "都道府県名"
        , max(case when rank = 1 then NATION_NAME end) as "1位 国名"
        , max(case when rank = 1 then AMT end)         as "1位 人数"
        , max(case when rank = 2 then NATION_NAME end) as "2位 国名"
        , max(case when rank = 2 then AMT end)         as "2位 人数"
        , max(case when rank = 3 then NATION_NAME end) as "3位 国名"
        , max(case when rank = 3 then AMT end)         as "3位 人数"
    from
        (
        select
              a.PF_CODE
            , PREFECTURE.PF_NAME
            , a.NATION_CODE
            , NATIONALITY.NATION_NAME
            , a.AMT
            , a.rank
        from
            (
            select
                *
            from
                (
                select *, row_number() over(partition by PF_CODE order by AMT desc, NATION_CODE asc) as rank
                from
                    (
                    select
                          PF_CODE
                        , NATION_CODE
                        , sum(AMT) as AMT
                    from
                        FOREIGNER
                    where
                        NATION_CODE <> '113'
                    group by PF_CODE, NATION_CODE
                    )
                )
            where
                rank <= 3
            ) a
            left outer join NATIONALITY using(NATION_CODE)
            left outer join PREFECTURE  using(PF_CODE)
        )
    group by PF_CODE, PF_NAME
    ) a
    left outer join
    (
    select
          PF_CODE as "都道府県コード"
        , sum(AMT) as AMT
    from
        FOREIGNER
    where
        NATION_CODE <> '113'
    group by PF_CODE
    ) b
    using("都道府県コード")
order by "合計人数" desc, a."都道府県コード" asc