WITH t AS
(
SELECT 1 id, '0401' dt, 'N' yn FROM dual
UNION ALL SELECT 1, '0408', 'N' FROM dual
UNION ALL SELECT 2, '0401', 'Y' FROM dual
UNION ALL SELECT 2, '0407', 'N' FROM dual
UNION ALL SELECT 3, '0401', 'Y' FROM dual
UNION ALL SELECT 3, '0407', 'Y' FROM dual
UNION ALL SELECT 3, '0409', 'N' FROM dual
UNION ALL SELECT 3, '0410', 'Y' FROM dual
)
SELECT *
  FROM (SELECT id, dt, yn
             , COUNT(*) OVER(PARTITION BY id, grp) cnt
             , ROW_NUMBER() OVER(PARTITION BY id, grp ORDER BY dt) rn
          FROM (SELECT id, dt, yn
                     , ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt)
                     - ROW_NUMBER() OVER(PARTITION BY id, yn ORDER BY dt)
                    AS grp
                  FROM t
                )
        )
 WHERE cnt > 1
;

DELETE FROM t
 WHERE (id, dt)
    IN (-- 삭제대상만 조회 --
        SELECT id, dt
          FROM (SELECT id, dt, yn
                     , DECODE(LAG(yn) OVER(PARTITION BY id ORDER BY dt)
                       , yn, 0, 1) flag
                  FROM t
                )
         WHERE flag = 0
        )
;