-- SELECT
--   PF_CODE
-- , NATION_CODE
-- , COALESCE(SUM(AMT), 0) AS SUM_AMT,
--   RANK () OVER ( 
--     ORDER BY SUM(AMT) 
--   ) RANK_AMT
-- FROM FOREIGNER
-- WHERE
--   NATION_CODE != 113
-- GROUP BY PF_CODE,	NATION_CODE;

WITH SUM_T AS
(
  SELECT
    FR2.PF_CODE AS PF_CODE
  -- , FR2.NATION_CODE
  , NATIONALITY.NATION_NAME AS NATION_NAME
  , COALESCE(FR2.AMT, 0) AS SUM_AMT
  , RANK () OVER (
      PARTITION BY PF_CODE
      ORDER BY FR2.AMT DESC
      , FR2.NATION_CODE ASC
    ) RANK_AMT
  FROM FOREIGNER AS FR2
  LEFT JOIN NATIONALITY ON
    NATIONALITY.NATION_CODE = FR2.NATION_CODE
  WHERE
      FR2.NATION_CODE != 113
    -- AND
    --   FR2.PF_CODE = PREFECTURE.PF_CODE
  GROUP BY FR2.PF_CODE, FR2.NATION_CODE
)
, SUM_AMT AS
(
  SELECT
    SUM(AMT) AS AMT
  , PF_CODE
  FROM FOREIGNER AS FR1
  WHERE
    FR1.NATION_CODE != 113
  GROUP BY FR1.PF_CODE
)

SELECT
  PREFECTURE.PF_CODE AS "都道府県コード"
, PREFECTURE.PF_NAME AS "都道府県名"
-- , SUM_T1.NATION_NAME
, SUM_T1.NATION_NAME AS "1位 国名"
, SUM_T1.SUM_AMT AS "1位 人数"
, SUM_T2.NATION_NAME AS "2位 国名"
, SUM_T2.SUM_AMT AS "2位 人数"
, SUM_T3.NATION_NAME AS "3位 国名"
, SUM_T3.SUM_AMT AS "3位 人数"
, SUM_AMT.AMT AS "合計人数"
FROM PREFECTURE

LEFT JOIN SUM_AMT ON
(
    SUM_AMT.PF_CODE = PREFECTURE.PF_CODE
  -- AND
  --   FR1.NATION_CODE != 113
)
LEFT JOIN SUM_T AS SUM_T1 ON
(
    SUM_T1.PF_CODE = PREFECTURE.PF_CODE
  AND
    SUM_T1.RANK_AMT = 1
)
LEFT JOIN SUM_T AS SUM_T2 ON
(
    SUM_T2.PF_CODE = PREFECTURE.PF_CODE
  AND
    SUM_T2.RANK_AMT = 2
)
LEFT JOIN SUM_T AS SUM_T3 ON
(
    SUM_T3.PF_CODE = PREFECTURE.PF_CODE
  AND
    SUM_T3.RANK_AMT = 3
)

-- WHERE NATION_CODE != 113
WHERE
  SUM_AMT.AMT IS NOT NULL
AND
  SUM_T1.SUM_AMT IS NOT NULL
AND
  SUM_T2.SUM_AMT IS NOT NULL
AND
  SUM_T3.SUM_AMT IS NOT NULL

ORDER BY
  "合計人数" DESC
, 
"都道府県コード" ASC
;