Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table employee(id integer, enroll_date date, total_employees integer);

insert into employee values (1,to_date('01-04-2023','DD-MM-YYYY'),10);
1 rows affected

insert into employee values (2,to_date('02-04-2023','DD-MM-YYYY'),109);
1 rows affected

insert into employee values (3,to_date('03-04-2023','DD-MM-YYYY'),150);
1 rows affected

insert into employee values (4,to_date('04-04-2023','DD-MM-YYYY'),99);
1 rows affected

insert into employee values (5,to_date('05-04-2023','DD-MM-YYYY'),145);
1 rows affected

insert into employee values (6,to_date('06-04-2023','DD-MM-YYYY'),1455);
1 rows affected

insert into employee values (7,to_date('07-04-2023','DD-MM-YYYY'),199);
1 rows affected

insert into employee values (8,to_date('08-04-2023','DD-MM-YYYY'),188);
1 rows affected

insert into employee values (10,to_date('10-04-2023','DD-MM-YYYY'),188);
1 rows affected

insert into employee values (12,to_date('12-04-2023','DD-MM-YYYY'),10);
1 rows affected

insert into employee values (13,to_date('13-04-2023','DD-MM-YYYY'),200);
1 rows affected

with cte as (
  select id, enroll_date, TOTAL_EMPLOYEES, lag(id) over (order by id) as prev_sal
  from employee
  where TOTAL_EMPLOYEES > 100
),
cte2 as (
  select id, enroll_date, TOTAL_EMPLOYEES, id - sum(case when id - PREV_SAL = 1 then 1 else 0 end) over(order by id) as grp
  from cte
)
select id, enroll_date, TOTAL_EMPLOYEES, grp
from cte2 t



ID
ENROLL_DATE
TOTAL_EMPLOYEES
GRP


2
02-APR-23
109
2

3
03-APR-23
150
2

5
05-APR-23
145
4

6
06-APR-23
1455
4

7
07-APR-23
199
4

8
08-APR-23
188
4

10
10-APR-23
188
6

13
13-APR-23
200
9


with cte as (
  select id, enroll_date, TOTAL_EMPLOYEES, lag(id) over (order by id) as prev_sal
  from employee
  where TOTAL_EMPLOYEES > 100
),
cte2 as (
  select id, enroll_date, TOTAL_EMPLOYEES, id - sum(case when id - PREV_SAL = 1 then 1 else 0 end) over(order by id) as grp
  from cte
)
select id, enroll_date, TOTAL_EMPLOYEES
from cte2 t
where grp in (
  select grp
  from cte2
  group by grp 
  having count(1) >= 3
)


ID
ENROLL_DATE
TOTAL_EMPLOYEES


5
05-APR-23
145

6
06-APR-23
1455

7
07-APR-23
199

8
08-APR-23
188

ID	ENROLL_DATE	TOTAL_EMPLOYEES	GRP
2	02-APR-23	109	2
3	03-APR-23	150	2
5	05-APR-23	145	4
6	06-APR-23	1455	4
7	07-APR-23	199	4
8	08-APR-23	188	4
10	10-APR-23	188	6
13	13-APR-23	200	9