select * 
  from t1 a
 where rowid > (select min(rowid) 
                  from t1 
                 where RESERVATION_ID = a.RESERVATION_ID 
                   and check_in_dt = a.check_in_dt)
  and (   check_in_dt >= '2021-09-28' 
       or (RESERVATION_ID = 'Ryaaaaa' and check_in_dt >='2021-09-25'))

앗! 괄호를 잘못 넣었네요. 마농님 감사합니다.~

테스트할 땐 잘 넣었던 것같은데 ^^:

SELECT *
  FROM t1 a
 WHERE check_in_dt >= '2021-09-25'
   AND check_in_dt >= DECODE(reservation_id, 'Ryaaaaa', '2021-09-25', '2021-09-28')
   AND EXISTS (SELECT 1
                 FROM t1
                WHERE reservation_id = a.reservation_id
                  AND check_in_dt    = a.check_in_dt
                  AND ROWID          > a.ROWID
               )
;

SELECT RESERVATION_ID, check_in_dt
FROM (SELECT RESERVATION_ID,  check_in_dt
      ,ROW_NUMBER() OVER(PARTITION BY RESERVATION_ID, check_in_dt) AS RN 
      FROM t1
      ) T
WHERE RN > 1
AND (check_in_dt >= '2021-09-28' OR RESERVATION_ID = 'Ryaaaaa')

특정 RESERVATION_ID (Ryaaaaa) 의 경우는 전체 날짜 기준, 나머지는 20210928 기준으로 중복되는 값들 중에 1개만 제외하고 나머지는 출력(삭제대상)하는 쿼리 입니다.(의도하셨던 질문의 답이 될지는 모르겠지만...)

위의 분들과는 또다른 쿼리가 되었네요

특정ID 만 별도 처리해 달라는 질문의 요구사항과
특정ID 를 조건절에 넣는것은 힘들것 같다는 댓글의 표현은
서로 상충되는 표현인데요?
원하시는 바에 대한 설명을 잘못하고 계신 듯 하네요.

with t1 as (
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-25' as check_in_dt, 'YES' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-25' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-26' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-27' as check_in_dt, 'YES' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-27' as check_in_dt, 'YES' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-27' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-09-28' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-10-03' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-10-04' as check_in_dt, 'YES' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-10-04' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryaaaaa' as RESERVATION_ID, '2021-10-05' as check_in_dt, 'NO' as del_target from dual union all
select 'Rybbbb' as RESERVATION_ID, '2021-01-30' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryccccc' as RESERVATION_ID, '2021-09-25' as check_in_dt, 'NO' as del_target from dual union all
select 'Ryccccc' as RESERVATION_ID, '2021-09-26' as check_in_dt, 'NO' as del_target from dual union all
select 'Rydddd' as RESERVATION_ID, '2021-10-06' as check_in_dt, 'NO' as del_target from dual
)
,TEMP AS (
SELECT DISTINCT RESERVATION_ID
FROM t1
WHERE check_in_dt < '2021-09-28'
)
SELECT RESERVATION_ID, check_in_dt
FROM (SELECT RESERVATION_ID,  check_in_dt
      ,ROW_NUMBER() OVER(PARTITION BY RESERVATION_ID, check_in_dt) AS RN 
      FROM t1
      ) T
WHERE RN > 1
AND (check_in_dt >= '2021-09-28' OR RESERVATION_ID IN (SELECT RESERVATION_ID FROM TEMP))
;

아마 특정 ID 가 아닌 해당 날짜 이전 기록을 가진 ID 값을 말씀하신거 같네요.

제가 이해한 것이 맞다면 이런 식의 쿼리는 어떨까 싶습니다.

답변 정말 감사드립니다.

조건절에 Ryaaaaa 이 직접 들어가서 조금 제 설명이 부족했던것 같습니다.

중복데이터 삭제 조건이 먼저

1. 2021/09/28 이후 데이터에 대해서 삭제.

2. 기본적으로 2021/09/28 이후 발생한 데이터에 대해서 "RESERVATION_ID" + "check_in_dt" 가 유일 하도록 삭제를 진행 하지만.

   예로 들은 Ryaaaaa 와 같이 2021/09/28 이후 발생 데이터중에 어떤 "RESERVATION_ID" 의 경우<예의 Ryaaaaa>는 최초  check_in_dt 데이터가 2021/09/28 이전부터도 발생한 경우가 있다. 이경우에는 해당 특정 RESERVATION_ID 의 경우는 최초 발생 데이터의 시점부터를 삭제 대상으로 한다. <예시로 든 Ryaaaaa경우는 2021/09/25 부터 .. >

3.  위 2번 조건으로부터, 아래 Ryccccc 의 경우는 중복 데이터가 있지만 Ryccccc 의 마지막 발생이 목표인 2021/09/28 이후에 발생하진 않았기 때문에 삭제 대상 제외.

1~3 을 요약하면  2021/09/28 이후 발생한 RESERVATION_ID 는  2021/09/28 이전 데이터도 전부 중복 제거 대상이 되겠네요.

입니다.  혹시 부족한 설명이 있다면 말씀주시면 추가 하겠습니다.

SELECT *
  FROM t1 a
 WHERE EXISTS (SELECT 1
                 FROM t1
                WHERE reservation_id = a.reservation_id
                  AND check_in_dt    = a.check_in_dt
                  AND ROWID          > a.ROWID
               )
   AND reservation_id IN (SELECT reservation_id
                            FROM t1
                           WHERE check_in_dt >= '2021-09-28'
                          )
;

자답입니다만 이런 느낌일까 싶었습니다.

select *
from t1 t
where exists (
            select 'x'
            from t1 x
            where x.RESERVATION_ID = t.RESERVATION_ID
            and x.check_in_dt = t.check_in_dt
            and x.rowid < t.rowid
            )
and (check_in_dt >= '2021-09-28' OR RESERVATION_ID in (
                                                        select RESERVATION_ID
                                                        from t1
                                                        group by RESERVATION_ID, check_in_dt
                                                        having count(*) > 1
                                                        )
    );