-- 外国人の入国者
WITH foreign_entrant AS (
    SELECT
        IG.PORT_CODE
        , IG.AMT AS entrant_amt
    FROM
        IMMIGRATION AS IG
    WHERE
        IG.KIND_CODE = 110  -- 入国者
        AND IG.GROUP_CODE = 120 -- 外国人
)
-- 外国人の出国者
, foreign_departures AS (
    SELECT
        IG.PORT_CODE
        , IG.AMT AS departures_amt
    FROM
        IMMIGRATION AS IG
    WHERE
        IG.KIND_CODE = 120  -- 出国者
        AND IG.GROUP_CODE = 120 -- 外国人
)
SELECT
    FE.PORT_CODE AS '港コード'
    , PORT.PORT_NAME AS '港名'
    , FE.entrant_amt AS '入国者数'
    , FD.departures_amt AS '出国者数'
    , FE.entrant_amt - FD.departures_amt AS '差分'
FROM
    foreign_entrant AS FE
INNER JOIN
    foreign_departures AS FD ON FE.PORT_CODE = FD.PORT_CODE
INNER JOIN
    PORT ON FE.PORT_CODE = PORT.PORT_CODE
WHERE
    (FE.entrant_amt - FD.departures_amt) > 0    -- 入国者が出国者より多い
ORDER BY
    (FE.entrant_amt - FD.departures_amt) DESC
    , FE.PORT_CODE DESC
;