By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable
(id int,status varchar(20), data varchar(3),date varchar(10));
insert into mytable values
( 1 , 'START' , 'a4c' , 'Jan 1'),
( 2 , 'WORKING' , '2w3' , 'Dec 29'),
( 3 , 'WORKING' , '2d3' , 'Dec 29'),
( 4 , 'WORKING' , '3ew' , 'Dec 26'),
( 5 , 'WORKING' , '5r5' , 'Dec 23'),
( 6 , 'START' , '2q3' , 'Dec 22'),
( 7 , 'WORKING' , '32w' , 'Dec 20'),
( 8 , 'WORKING' , '9k5' , 'Dec 10');
Records: 8 Duplicates: 0 Warnings: 0
select id, status,
(select count(*)
from mytable t2
where t2.id > t.id and t2.status='WORKING'
and not exists (select 1
from mytable t3
where t3.id > t.id and t3.id < t2.id and status='START')
) count,
date
from mytable t
where status='START'
| id | status | count | date |
|---|---|---|---|
| 1 | START | 4 | Jan 1 |
| 6 | START | 2 | Dec 22 |