SELECT
  pf_code AS "都道府県コード",
  pf_name AS "都道府県名",
  max (
    case
      sub.rank
      WHEN 1 THEN nation_name
      ELSE NULL
    end
  ) AS "1位 国名",
  max (
    case
      sub.rank
      WHEN 1 THEN amt
      ELSE NULL
    end
  ) AS "1位 人数",
  max (
    case
      sub.rank
      WHEN 2 THEN nation_name
      ELSE NULL
    end
  ) AS "2位 国名",
  max (
    case
      sub.rank
      WHEN 2 THEN amt
      ELSE NULL
    end
  ) AS "2位 人数",
  max (
    case
      sub.rank
      WHEN 3 THEN nation_name
      ELSE NULL
    end
  ) AS "3位 国名",
  max (
    case
      sub.rank
      WHEN 3 THEN amt
      ELSE NULL
    end
  ) AS "3位 人数",
  sum(amt) AS "合計人数"
FROM
  (
    SELECT
      f.pf_code AS pf_code,
      p.pf_name AS pf_name,
      n.nation_name AS nation_name,
      f.amt AS amt,
      rank() over (
        PARTITION by f.pf_code
        ORDER BY
          f.amt DESC,
          f.nation_code
      ) AS rank
    FROM
      foreigner f
      JOIN prefecture p ON f.pf_code = p.pf_code
      JOIN nationality n ON f.nation_code = n.nation_code
    WHERE
      f.nation_code <> '113'
  ) AS sub
GROUP BY
  sub.pf_code
ORDER BY
  "合計人数" DESC,
  "都道府県コード" ASC;