By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
create table employee(id integer, enroll_date date, total_employees integer);
BEGIN
insert into employee values (1,to_date('01-04-2023','DD-MM-YYYY'),10);
insert into employee values (2,to_date('02-04-2023','DD-MM-YYYY'),109);
insert into employee values (3,to_date('03-04-2023','DD-MM-YYYY'),150);
insert into employee values (4,to_date('04-04-2023','DD-MM-YYYY'),99);
insert into employee values (5,to_date('05-04-2023','DD-MM-YYYY'),145);
insert into employee values (6,to_date('06-04-2023','DD-MM-YYYY'),1455);
insert into employee values (7,to_date('07-04-2023','DD-MM-YYYY'),199);
insert into employee values (8,to_date('08-04-2023','DD-MM-YYYY'),188);
insert into employee values (10,to_date('10-04-2023','DD-MM-YYYY'),188);
insert into employee values (12,to_date('12-04-2023','DD-MM-YYYY'),10);
insert into employee values (13,to_date('13-04-2023','DD-MM-YYYY'),200);
END;
/
1 rows affected
SELECT *
FROM employee
MATCH_RECOGNIZE(
ORDER BY enroll_date
ALL ROWS PER MATCH
PATTERN ( consecutive{2,} last_row )
DEFINE
consecutive AS id + 1 = NEXT(id)
AND total_employees > 100,
last_row AS total_employees > 100
)
| ENROLL_DATE | ID | TOTAL_EMPLOYEES |
|---|---|---|
| 2023-04-05 00:00:00 | 5 | 145 |
| 2023-04-06 00:00:00 | 6 | 1455 |
| 2023-04-07 00:00:00 | 7 | 199 |
| 2023-04-08 00:00:00 | 8 | 188 |
SELECT *
FROM (SELECT * FROM employee WHERE total_employees > 100)
MATCH_RECOGNIZE(
ORDER BY enroll_date
ALL ROWS PER MATCH
PATTERN ( consecutive{2,} last_row )
DEFINE consecutive AS id + 1 = NEXT(id)
)
| ENROLL_DATE | ID | TOTAL_EMPLOYEES |
|---|---|---|
| 2023-04-05 00:00:00 | 5 | 145 |
| 2023-04-06 00:00:00 | 6 | 1455 |
| 2023-04-07 00:00:00 | 7 | 199 |
| 2023-04-08 00:00:00 | 8 | 188 |