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