with
  a as (
    select
      pf_code,
      nation_code,
      rank() over (
        partition by
          pf_code
        order by
          amt desc,
          nation_code
      ) as rnk,
    amt
    from
      foreigner
    where
      nation_code <> '113'
  )
select
  a.PF_CODE as "都道府県コード",
  pre.PF_name as "都道府県名",
  max( case when rnk == 1 then nation_name else null end) as "1位 国名",
  max( case when rnk == 1 then amt else null end) as "1位 人数",
  max( case when rnk == 2 then nation_name else null end) as "2位 国名",
  max( case when rnk == 2 then amt else null end) as "2位 人数",
  max( case when rnk == 3 then nation_name else null end) as "3位 国名",
  max( case when rnk == 3 then amt else null end) as "3位 人数",
  sum(amt) as "合計人数"
from
  a
  join nationality na on a.nation_code = na.nation_code
  join prefecture pre on a.pf_code = pre.pf_code
group by
  a.pf_code
order by 9 desc,1