WITH ranked_nations AS (
  SELECT
    f.PF_CODE,
    p.PF_NAME,
    n.NATION_NAME,
    f.AMT,
    RANK() OVER (
      PARTITION BY f.PF_CODE
      ORDER BY f.AMT DESC, f.NATION_CODE ASC
    ) AS rank
  FROM
    FOREIGNER f
    JOIN NATIONALITY n ON f.NATION_CODE = n.NATION_CODE
    JOIN PREFECTURE p ON f.PF_CODE = p.PF_CODE
  WHERE
    f.NATION_CODE <> '113'
),
totals AS (
  SELECT
    PF_CODE,
    SUM(AMT) AS total_amt
  FROM
    FOREIGNER
  WHERE
    NATION_CODE <> '113'
  GROUP BY
    PF_CODE
)
SELECT
  PF_CODE AS "都道府県コード",
  PF_NAME AS "都道府県名",
  MAX(CASE WHEN rank = 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名",
  MAX(CASE WHEN rank = 1 THEN AMT ELSE 0 END) AS "1位 人数",
  MAX(CASE WHEN rank = 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名",
  MAX(CASE WHEN rank = 2 THEN AMT ELSE 0 END) AS "2位 人数",
  MAX(CASE WHEN rank = 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名",
  MAX(CASE WHEN rank = 3 THEN AMT ELSE 0 END) AS "3位 人数",
  SUM(AMT) AS "合計人数"
FROM
  ranked_nations
GROUP BY
  PF_CODE,
  PF_NAME
ORDER BY
  "合計人数" DESC,
  "都道府県コード" ASC;