with immigrations as (
    select
        port_code,
        sum(case when kind_code = '110' then amt else 0 end) as in_amount,
        sum(case when kind_code = '120' then amt else 0 end) as out_amount
    from
        immigration
    where
        group_code = '120' -- 外国人
    group by
        port_code
)
select
    I.port_code as 港コード,
    P.port_name as 港名,
    I.in_amount as 入国者数,
    I.out_amount as 出国者数,
    I.in_amount - I.out_amount as 差分
from
    immigrations I
    join port P
    on I.port_code = P.port_code
where
    I.in_amount > I.out_amount
order by
    差分 desc
;