WITH 
RANKING AS(
SELECT
    *,
    RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC) AS RANK
FROM FOREIGNER
WHERE NATION_CODE != 113
),
FIRST AS (
SELECT
PF_CODE,
NATION_CODE AS FIRST_NATION_CODE,
NATION_NAME AS FIRST_NATION_NAME,
AMT AS FIRST_AMT,
RANK AS FIRST_RANK
FROM RANKING
LEFT JOIN NATIONALITY USING(NATION_CODE)
WHERE RANK = 1
),

SECOND AS (
SELECT
PF_CODE,
NATION_CODE AS SECOND_NATION_CODE,
NATION_NAME AS SECOND_NATION_NAME,
AMT AS SEC_AMT,
RANK AS SEC_RANK
FROM RANKING
LEFT JOIN NATIONALITY USING(NATION_CODE)
WHERE RANK = 2
),

THIRD AS (
SELECT
PF_CODE,
NATION_CODE AS THIRD_NATION_CODE,
NATION_NAME AS THIRD_NATION_NAME,
AMT AS THIRD_AMT,
RANK AS THIRD_RANK
FROM RANKING
LEFT JOIN NATIONALITY USING(NATION_CODE)
WHERE RANK = 3
),
TOTAL AS(
SELECT
    PF_CODE,
    SUM(AMT) AS TOTAL_AMT
FROM FOREIGNER
WHERE NATION_CODE != 113
GROUP BY 1
)


SELECT
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    FIRST_NATION_NAME AS '1位 国名',
    FIRST_AMT AS '1位 人数',
    SECOND_NATION_NAME AS '2位 国名',
    SEC_AMT AS '2位 人数',
    THIRD_NATION_NAME AS '3位 国名',
    THIRD_AMT AS '3位 人数',
    TOTAL_AMT AS '合計人数'
FROM PREFECTURE
JOIN FIRST USING (PF_CODE)
JOIN SECOND USING (PF_CODE)
JOIN THIRD USING (PF_CODE)
JOIN TOTAL USING(PF_CODE)
ORDER BY TOTAL_AMT DESC, PF_CODE ASC