SELECT
  t1.PF_CODE AS '都道府県コード'
  , t1.PF_NAME AS '都道府県名'
  , t2.`1位 国名` AS '1位 国名'
  , t2.`1位 人数` AS '1位 人数'
  , t2.`2位 国名` AS '2位 国名'
  , t2.`2位 人数` AS '2位 人数'
  , t2.`3位 国名` AS '3位 国名'
  , t2.`3位 人数` AS '3位 人数'
  , t3.TTL AS '合計人数'
FROM
  PREFECTURE t1
INNER JOIN
  (SELECT
    s1.PF_CODE AS PF_CODE
    , s2.NATION_NAME AS NATION_NAME
  , FIRST_VALUE(s2.NATION_NAME) OVER (
    PARTITION BY s1.PF_CODE
    ORDER BY s1.AMT DESC, s1.PF_CODE ASC
  ) AS '1位 国名'
  , FIRST_VALUE(s1.AMT) OVER (
    PARTITION BY s1.PF_CODE
    ORDER BY s1.AMT DESC, s1.PF_CODE ASC
  ) AS '1位 人数'
  , NTH_VALUE(s2.NATION_NAME, 2) OVER (
    PARTITION BY s1.PF_CODE
    ORDER BY s1.AMT DESC, s1.PF_CODE ASC
  ) AS '2位 国名'
  , NTH_VALUE(s1.AMT, 2) OVER (
    PARTITION BY s1.PF_CODE
    ORDER BY s1.AMT DESC, s1.PF_CODE ASC
  ) AS '2位 人数'
  , NTH_VALUE(s2.NATION_NAME, 3) OVER (
    PARTITION BY s1.PF_CODE
    ORDER BY s1.AMT DESC, s1.PF_CODE ASC
  ) AS '3位 国名'
  , NTH_VALUE(s1.AMT, 3) OVER (
    PARTITION BY s1.PF_CODE
    ORDER BY s1.AMT DESC, s1.PF_CODE ASC
  ) AS '3位 人数'
  FROM FOREIGNER s1
  INNER JOIN NATIONALITY s2 ON s1.NATION_CODE = s2.NATION_CODE
  WHERE s1.NATION_CODE <> '113'
) t2
ON t1.PF_CODE = t2.PF_CODE
INNER JOIN
  (SELECT PF_CODE AS PF_CODE, SUM(AMT) AS TTL FROM FOREIGNER WHERE NATION_CODE <> '113') t3
ON t1.PF_CODE = t3.PF_CODE
WHERE
t2.`1位 国名`IS NOT NULL
AND t2.`1位 人数`IS NOT NULL
AND t2.`2位 国名`IS NOT NULL
AND t2.`2位 人数`IS NOT NULL
AND t2.`3位 国名`IS NOT NULL
AND t2.`3位 人数`IS NOT NULL
GROUP BY
  t1.PF_CODE, t1.PF_NAME
ORDER BY
  t3.TTL DESC
  , t1.PF_CODE ASC
;