WITH RANKING AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, PF_CODE) AS RANKING
  FROM
    FOREIGNER
  WHERE
    NATION_CODE <> '113'
)
, TOTAL AS (
  SELECT
    PF_CODE,
    SUM(AMT) AS AMT_SUM
  FROM
    RANKING
  GROUP BY
    PF_CODE
)
, TOP_N AS (
  SELECT DISTINCT
    T1.PF_CODE,
    (SELECT NATION_CODE FROM RANKING WHERE PF_CODE = T1.PF_CODE AND RANKING = 1 LIMIT 1) AS NATION_CODE1,
    (SELECT AMT FROM RANKING WHERE PF_CODE = T1.PF_CODE AND RANKING = 1 LIMIT 1) AS AMT1,
    (SELECT NATION_CODE FROM RANKING WHERE PF_CODE = T1.PF_CODE AND RANKING = 2 LIMIT 1) AS NATION_CODE2,
    (SELECT AMT FROM RANKING WHERE PF_CODE = T1.PF_CODE AND RANKING = 2 LIMIT 1) AS AMT2,
    (SELECT NATION_CODE FROM RANKING WHERE PF_CODE = T1.PF_CODE AND RANKING = 3 LIMIT 1) AS NATION_CODE3,
    (SELECT AMT FROM RANKING WHERE PF_CODE = T1.PF_CODE AND RANKING = 3 LIMIT 1) AS AMT3
  FROM
    RANKING AS T1
)
SELECT
  T1.PF_CODE AS 都道府県コード,
  T2.PF_NAME AS 都道府県名,
  T4.NATION_NAME AS "1位 国名",
  T1.AMT1 AS "1位 人数",
  T5.NATION_NAME AS "2位 国名",
  T1.AMT2 AS "2位 人数",
  T6.NATION_NAME AS "3位 国名",
  T1.AMT3 AS "3位 人数",
  T3.AMT_SUM AS 合計人数
FROM
  TOP_N AS T1
INNER JOIN
  PREFECTURE AS T2
ON
  T1.PF_CODE = T2.PF_CODE
INNER JOIN
  TOTAL AS T3
ON
  T1.PF_CODE = T3.PF_CODE
INNER JOIN
  NATIONALITY AS T4
ON
  T1.NATION_CODE1 = T4.NATION_CODE
INNER JOIN
  NATIONALITY AS T5
ON
  T1.NATION_CODE2 = T5.NATION_CODE
INNER JOIN
  NATIONALITY AS T6
ON
  T1.NATION_CODE3 = T6.NATION_CODE
ORDER BY
  合計人数 DESC,
  T1.PF_CODE
;