-- 港毎に出入国者の人数を集計した出入国集計データより、外国人の入国者が出国者より多い港を抽出しなさい。 種別コードは、'110'(入国者) 、'120'(出国者)となる。

-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)

-- PORT_CODE → 港コード
-- PORT_NAME → 港名
-- 外国人入国者のAMT → 入国者数
-- 外国人出国者のAMT → 出国者数
-- 外国人入国者のAMT - 外国人出国者のAMTの計算結果 → 差分
-- 表示順

-- 差分の降順
-- 港コードの降順
WITH base AS (
    SELECT
        PORT_CODE AS "港コード"
        , PORT_NAME AS "港名"
        , SUM(CASE WHEN GROUP_CODE = '120' AND KIND_CODE ='110' THEN AMT ELSE 0 END) AS "入国者数"
        , SUM(CASE WHEN GROUP_CODE = '120' AND KIND_CODE ='120' THEN AMT ELSE 0 END) AS "出国者数"

    FROM
        IMMIGRATION
    
    LEFT JOIN
        GRP
    USING (GROUP_CODE)
        
    LEFT JOIN
        PORT
    USING (PORT_CODE)
    
    GROUP BY
        PORT_CODE
        , PORT_NAME
)
SELECT
    "港コード"
    , "港名"
    , "入国者数"
    , "出国者数"
    , "入国者数" - "出国者数"  AS "差分"

FROM
    base

WHERE
    "入国者数" > "出国者数"

ORDER BY
    "差分" DESC
    ,"港コード" DESC