/*
SELECT
    PF_CODE AS "都道府県コード",
    PF_NAME AS "都道府県名",
    MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME END) AS "1位 国名",
    MAX(CASE SUB.RANK WHEN 1 THEN AMT END) AS "1位 人数",
    MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME END) AS "2位 国名",
    MAX(CASE SUB.RANK WHEN 2 THEN AMT END) AS "2位 人数",
    MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME END) AS "3位 国名",
    MAX(CASE SUB.RANK WHEN 3 THEN AMT END) AS "3位 人数",
    SUM(AMT) AS "合計人数"
FROM
    (SELECT
        F.PF_CODE AS PF_CODE,
        P.PF_NAME AS PF_NAME,
        N.NATION_NAME AS NATION_NAME,
        F.AMT AS AMT,
        RANK() OVER (PARTITION BY F.PF_CODE ORDER BY AMT DESC, F.NATION_CODE ASC) AS RANK
    FROM
        FOREIGNER AS F
        INNER JOIN
            NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE
        INNER JOIN
            PREFECTURE AS P ON P.PF_CODE = F.PF_CODE
    WHERE
        F.NATION_CODE != "113"
    ) AS SUB
GROUP BY
    SUB.PF_CODE
ORDER BY
    "合計人数" DESC,
    "都道府県コード" ASC;

*/
SELECT
    F.PF_CODE AS PF_CODE,
    P.PF_NAME AS PF_NAME,
    N.NATION_NAME AS NATION_NAME,
    F.AMT AS AMT,
    RANK() OVER (PARTITION BY F.PF_CODE ORDER BY AMT DESC, F.NATION_CODE ASC) AS RANK
    RANK() OVER (ORDER BY AMT DESC, F.NATION_CODE ASC) AS RANK_ALL
FROM
    FOREIGNER AS F
    INNER JOIN
        NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE
    INNER JOIN
        PREFECTURE AS P ON P.PF_CODE = F.PF_CODE
WHERE
    F.NATION_CODE != "113"