-- SELECT 
--     PF_CODE '都道府県コード',
--     PF_NAME '都道府県名',
--     人数が一番多い国のNATION_NAME '1位 国名',
--     人数が一番多い国のAMT '1位 人数',
--     人数が二番目に多い国のNATION_NAME '2位 国名',
--     人数が二番目に多い国のAMT '2位 人数',
--     人数が三番目に多い国のNATION_NAME '3位 国名',
--     人数が三番目に多い国のAMT '3位 人数',
--     各都道府県毎の外国人の合計人数 '合計人数'
-- FROM FOREIGNER F
--     INNER JOIN PREFECTURE P ON F.PF_CODE = P.PF_CODE
--     INNER JOIN NATIONALITY N ON F.NATION_CODE = N.NATION_CODE
-- GROUP BY F.PF_CODE
WITH RANKINGS AS (
    SELECT RANK,PF_CODE,NATION_NAME,AMT FROM (
        SELECT
            RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, F.NATION_CODE ASC) RANK,
            PF_CODE,
            NATION_NAME,
            AMT
        FROM FOREIGNER F
            INNER JOIN NATIONALITY N ON F.NATION_CODE = N.NATION_CODE
        WHERE F.NATION_CODE != 113
    )
    WHERE RANK <= 3
)
SELECT 
    P.PF_CODE '都道府県コード',
    P.PF_NAME '都道府県名',
    R_1.NATION_NAME '1位 国名',
    R_1.AMT '1位 人数',
    R_2.NATION_NAME '2位 国名',
    R_2.AMT '2位 人数',
    R_3.NATION_NAME '3位 国名',
    R_3.AMT '3位 人数',
    TOTAL.TOTAL_AMT '合計人数'
FROM PREFECTURE P
    INNER JOIN (SELECT PF_CODE, NATION_NAME, AMT FROM RANKINGS WHERE RANK=1) R_1 ON P.PF_CODE = R_1.PF_CODE
    INNER JOIN (SELECT PF_CODE, NATION_NAME, AMT FROM RANKINGS WHERE RANK=2) R_2 ON P.PF_CODE = R_2.PF_CODE
    INNER JOIN (SELECT PF_CODE, NATION_NAME, AMT FROM RANKINGS WHERE RANK=3) R_3 ON P.PF_CODE = R_3.PF_CODE
    INNER JOIN (SELECT PF_CODE, SUM(AMT) TOTAL_AMT FROM FOREIGNER WHERE NATION_CODE != 113 GROUP BY PF_CODE) TOTAL ON P.PF_CODE = TOTAL.PF_CODE
ORDER BY
    TOTAL.TOTAL_AMT DESC,
    P.PF_CODE ASC