with tg_group as (
select
    GROUP_CODE
from
    GRP
where
    GROUP_CODE in ("120")
)
, tg_amt as (
select
    PORT_CODE
    ,GROUP_CODE
    ,KIND_CODE
    ,AMT
from
    IMMIGRATION
where
    KIND_CODE in ("110", "120")
)
, join_code as (
select
    a.PORT_CODE
    ,PORT_NAME
    ,KIND_CODE
    ,AMT
from
    tg_amt a
inner join
    tg_group b
on
    a.GROUP_CODE = b.GROUP_CODE
inner join
    PORT c
on
    a.PORT_CODE = c.PORT_CODE
)
, agg1 as (
select
    PORT_CODE
    ,PORT_NAME
    ,sum(case when KIND_CODE = "110" then AMT else 0 end) as "入国者数"
    ,sum(case when KIND_CODE = "120" then AMT else 0 end) as "出国者数"
    
from
    join_code
group by
    PORT_CODE
    ,PORT_NAME
)
, agg_diff as (
select
    PORT_CODE as "港コード"
    ,PORT_NAME as "港名"
    ,"入国者数"
    ,"出国者数"
    ,"入国者数" - "出国者数" as "差分"
from
    agg1
where
    "入国者数" > "出国者数"
)
select
    *
from
    agg_diff
order by
    "差分" desc
    ,"港コード" desc