with tmp as(
select
    PORT_CODE as 港コード,
    sum(CASE WHEN KIND_CODE == "110" THEN AMT else null end) as 入国者数,
    sum(CASE WHEN KIND_CODE == "120" THEN AMT else null end) as 出国者数
from
    IMMIGRATION
where
    KIND_CODE in ("110", "120") 
    AND GROUP_CODE == "120"
group by
    PORT_CODE
having
    入国者数 > 出国者数
)

select
    港コード,
    p.PORT_NAME as 港名,
    入国者数,
    出国者数,
    入国者数 - 出国者数 as 差分
from 
    tmp
left join
    PORT as p on tmp.港コード = p.PORT_CODE
order by
    差分 desc,
    港コード desc
;