Consecutive rows having more than 100 employees

May 2, 2023

write a oracle sql query to get three or more consecutive ids having num of employees > 100. For example, in the below data, we need rows having ids – 5,6,7,8
Explanation: rows with id 5,6,7,8 are consecutive and have >100 total_employees. However id 10 or 12 is not selected even though total_employees > 100, because they are not consecutive.

Input Data

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);
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);

tried to attempt below to get consecutive groups and flag for employee count, however not figuring out to get desired results.

select id, enroll_date,total_employees,
        case when total_employees>100 then 1 else 0 end emp_flag,
        SUM(case when total_employees>100 then 1 else 0 end) OVER (ORDER BY id) AS grp,
        id - row_number() over(order by id) as diff, -- group consecutive id's
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN total_employees > 100
                           THEN 1 ELSE 0 END ORDER BY enroll_date) as sal_rn,
        id - ROW_NUMBER() OVER (PARTITION BY CASE WHEN total_employees > 100
                           THEN 1 ELSE 0 END ORDER BY enroll_date) AS sal_grp
from employee
;

>Solution :

Here’s one option; read comments within code.

SQL> with temp as
  2    -- eliminate rows by number of total employees and create groups (islands)
  3    (select b.*,
  4       row_number() over (order by id) - id grp
  5     from employee b
  6     where total_employees > 100
  7    ),
  8  temp2 as
  9    -- count number of members per group
 10    (select a.*,
 11       count(*) over (partition by grp) cnt
 12     from temp a
 13    )
 14  -- finally, extract rows that have 3 or more consecutive rows in the same group
 15  select id, enroll_date, total_employees
 16  from temp2
 17  where cnt >= 3
 18  order by id;

        ID ENROLL_DAT TOTAL_EMPLOYEES
---------- ---------- ---------------
         5 05-04-2023             145
         6 06-04-2023            1455
         7 07-04-2023             199
         8 08-04-2023             188

SQL>