By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test
(id int, status varchar(8), data char(3),date varchar(6));
insert into test 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, `count`, `date`
FROM ( SELECT @count `count`,
id,
status,
`date`,
@count:=(@status=status)*@count+1,
@status:=status
FROM test,
( SELECT @count:=0, @status:='' ) init_vars
ORDER BY id DESC
) calculations
WHERE status='START'
ORDER BY id
| id | status | count | date |
|---|---|---|---|
| 1 | START | 4 | Jan 1 |
| 6 | START | 2 | Dec 22 |
WITH cte AS (
SELECT id,
status,
`date`,
SUM(status='WORKING') OVER (ORDER BY id DESC) workings
FROM test
ORDER BY id )
SELECT id,
status,
workings - COALESCE(LEAD(workings) OVER (ORDER BY id), 0) `count`,
`date`
FROM cte
WHERE status='START'
ORDER BY id
| id | status | count | date |
|---|---|---|---|
| 1 | START | 4 | Jan 1 |
| 6 | START | 2 | Dec 22 |