with
  -- 外国人だけのデータ(immigrationテーブル)
  foreigner_data as (
    select 
      i.port_code,
      i.group_code,
      i.kind_code,
      i.amt
    from
      immigration i
    where 
      i.group_code = 120
  ), 
  -- 出国者データ
  departure_immigration as (
    select 
      fd.port_code,
      fd.group_code,
      fd.kind_code,
      fd.amt
    from 
      foreigner_data fd
    where
      fd.kind_code = 120
  ), 
  -- 入国者データ
  entrant_immigration as (
    select 
      fd.port_code,
      fd.group_code,
      fd.kind_code,
      fd.amt
    from
      foreigner_data fd
    where
      fd.kind_code = 110
  )
select 
  p.port_code as 港コード,
  p.port_name as 港名,
  ei.amt as 入国者数,
  di.amt as 出国者数,
  ei.amt - di.amt as 差分
from 
  entrant_immigration ei
left outer join port p on ei.port_code = p.port_code
left outer join departure_immigration di on ei.port_code = di.port_code
where ei.amt > di.amt
order by 差分 desc, 港コード desc;