Nice. But it's still not right if you include your original test data:
create table testtable
(
x int,
dt date
);
Insert into testtable (X,DT) values (100,to_date('01-JAN-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('02-FEB-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('03-MAR-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('04-APR-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('05-MAY-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('02-JAN-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('27-JAN-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('05-FEB-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('15-FEB-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('15-FEB-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('08-MAR-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
Insert into testtable (X,DT) values (100,to_date('18-MAR-2017 00:00:00','DD-MON-RRRR HH24:MI:SS'));
with set1 as
(
select x,dt,drnk ,case when drnk=1 then 0 else lagdays end lagdays
from
(
select x,dt,
dense_rank() over(partition by x order by dt) drnk,
dt-lag(dt, 1, date'1900-01-01') over (partition by x order by dt) lagdays
FROM testtable
)),
set2 as
(
select x,dt,drnk,lagdays,trunc(rt/30) bucket from
(
select x,dt,drnk,lagdays,
SUM(case when lagdays>30 then 30 else lagdays end) OVER(partition by x ORDER BY drnk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rt
from
set1
))
select x,dt from
(
select x,dt,drnk,lagdays,bucket,
dense_rank() over(partition by x,bucket order by dt) bucketrank
from set2
)
where bucketrank=1
order by x,dt;
X DT
100 01-JAN-2017 00:00:00
100 02-FEB-2017 00:00:00
100 03-MAR-2017 00:00:00 <==== 3 Mar again! Shouldn't appear...
100 04-APR-2017 00:00:00
100 05-MAY-2017 00:00:00