WITH STOCK_INFO AS (
    SELECT ITEM_CODE
          ,MAX(LAST_DELIVERY_DATE) AS LAST_DELIVERY_DATE
          ,SUM(ACTUAL_AMT) AS ACTUAL_AMT
         FROM STOCK
        GROUP BY ITEM_CODE
        ORDER BY ITEM_CODE
)
,agg AS (
    SELECT *
        FROM STOCK_INFO
    WHERE LAST_DELIVERY_DATE <= DATE('2023-06-01','-6 months') --出荷日が6か月以前
    AND ACTUAL_AMT = 0 --在庫が0である
)
SELECT a.*
    FROM ITEM AS a
    LEFT JOIN agg AS b
    ON a.ITEM_CODE = b.ITEM_CODE
    JOIN (
    SELECT DISTINCT ITEM_CODE FROM STOCK 
    ) AS c --STOCKに在庫があるもの
    ON a.ITEM_CODE = c.ITEM_CODE
WHERE ACTUAL_AMT IS NULL
AND STOCK_MANAGEMENT_TYPE = 1
ORDER BY ITEM_CODE