select
        PF_CODE as "都道府県コード"
        , PF_NAME as "都道府県名"
        , MAX(IIF(RANK=1,NATION_NAME,'')) as "1位 国名"
        , MAX(IIF(RANK=1,AMT,0)) as "1位 人数"
        , MAX(IIF(RANK=2,NATION_NAME,'')) as "2位 国名"
        , MAX(IIF(RANK=2,AMT,0)) as "2位 人数"
        , MAX(IIF(RANK=3,NATION_NAME,'')) as "3位 国名"
        , MAX(IIF(RANK=3,AMT,0)) as "3位 人数"
        , MAX(IIF(RANK=1,TOTAL_AMT, 0)) as "合計人数"
    from 
        (
        select
            FORE.PF_CODE
            , PRE.PF_NAME
            , FORE.NATION_CODE
            , NAT.NATION_NAME
            , AMT
            , RANK
            , TOT.TOTAL_AMT
        FROM
            (
            select
                PF_CODE
                ,NATION_CODE
                ,SUM(AMT) AMT
                ,RANK() OVER (PARTITION BY PF_CODE ORDER BY SUM(AMT) DESC) AS RANK
            from 
                FOREIGNER
            WHERE
                NATION_CODE <> '113'
            GROUP BY 
                PF_CODE, NATION_CODE
            ) FORE
            INNER JOIN PREFECTURE PRE ON FORE.PF_CODE = PRE.PF_CODE
            INNER JOIN NATIONALITY NAT ON FORE.NATION_CODE = NAT.NATION_CODE
            INNER JOIN (
                select
                    PF_CODE
                    , SUM(AMT) as TOTAL_AMT
                from 
                    FOREIGNER
                WHERE
                    NATION_CODE <> '113'
                GROUP BY 
                    PF_CODE
            ) TOT ON FORE.PF_CODE = TOT.PF_CODE
        WHERE
            RANK < 4
        )
    GROUP BY
        PF_CODE
    ORDER BY
        TOTAL_AMT desc, PF_CODE