WITH rank_t AS 
  (
    SELECT
      PF_CODE,
      NATION_CODE,
      ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS rank0,
      AMT
    FROM
      FOREIGNER
    WHERE
      NATION_CODE != 113
  ),
rank1 AS 
  (
    SELECT 
      PF_CODE,
      NATION_NAME,
      AMT
    FROM
      rank_t
    INNER JOIN 
      NATIONALITY
    ON 
      rank_t.NATION_CODE = NATIONALITY.NATION_CODE
    WHERE
      rank0 = 1
  ),
rank2 AS 
  (
    SELECT 
      PF_CODE,
      NATION_NAME,
      AMT
    FROM
      rank_t
    INNER JOIN 
      NATIONALITY
    ON 
      rank_t.NATION_CODE = NATIONALITY.NATION_CODE
    WHERE
      rank0 = 2
  ),
rank3 AS 
  (
    SELECT 
      PF_CODE,
      NATION_NAME,
      AMT
    FROM
      rank_t
    INNER JOIN 
      NATIONALITY
    ON 
      rank_t.NATION_CODE = NATIONALITY.NATION_CODE
    WHERE
      rank0 = 3
  ),
allsum AS
  (
    SELECT 
      PF_CODE,
      SUM(AMT) AS sum_amt
    FROM 
      FOREIGNER
    GROUP BY 
      PF_CODE
  )

SELECT 
  t1.PF_CODE AS 都道府県コード,
  PREFECTURE.PF_NAME AS 都道府県名,
  rank1.NATION_NAME AS "1位 国名",
  rank1.AMT AS "1位 人数",
  rank2.NATION_NAME AS "2位 国名",
  rank2.AMT AS "2位 人数",
  rank3.NATION_NAME AS "3位 国名",
  rank3.AMT AS "3位 人数",
  sum_amt AS "合計人数"
FROM 
  (
    SELECT DISTINCT
      PF_CODE
    FROM 
      PREFECTURE
  ) AS t1
INNER JOIN 
  rank1
ON 
  t1.PF_CODE = rank1.PF_CODE
INNER JOIN
  rank2
ON 
  t1.PF_CODE = rank2.PF_CODE
INNER JOIN
  rank3
ON 
  t1.PF_CODE = rank3.PF_CODE
INNER JOIN
  PREFECTURE
ON 
  t1.PF_CODE = PREFECTURE.PF_CODE
INNER JOIN 
  allsum
ON 
  t1.PF_CODE = allsum.PF_CODE
ORDER BY 
  合計人数 DESC,
  都道府県コード