-- https://topsic-contest.jp/contests/contest00x/problems/contest00x-3

select
    imi.port_code as 港コード,
    port.port_name 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 出国者数,
    (sum(case when kind_code == 110 then amt else null end) - sum(case when kind_code == 120 then amt else null end)) as 差分
from
    immigration imi 
left join 
    port 
on imi.port_code = port.port_code
group by 
    imi.port_code,
    port.port_name
having (sum(case when kind_code == 110 then amt else null end) - sum(case when kind_code == 120 then amt else null end)) >0
order by 
    (sum(case when kind_code == 110 then amt else null end) - sum(case when kind_code == 120 then amt else null end)) desc,
    port.port_code desc
    ;