SQL Server 2019

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

create table #x (_id int identity, _category int, _date date, _value int)

insert into #x values 
(1, '2022-10-01', 12),(1, '2022-10-03', 0),(1, '2022-10-04', 10),(1, '2022-10-06', 11),(1, '2022-10-07', 10),
(2, '2022-10-01', 1),(2, '2022-10-02', 0),(2, '2022-10-05', 19),(2, '2022-10-10', 18),(2, '2022-10-12', 0),(2, '2022-10-13', 0),(2, '2022-10-15', 0),
(3, '2022-10-02', 10),(3, '2022-10-03', 0),(3, '2022-10-05', 0),(3, '2022-10-06', 12),(3, '2022-10-08', 0);
17 rows affected

WITH cte AS (
  SELECT *, 
         MAX(_value) OVER (
           PARTITION BY _category 
           ORDER BY _date 
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
         ) max
  FROM #x
)  
DELETE FROM cte
WHERE max = 0;
4 rows affected

SELECT * FROM #x;


_id
_category
_date
_value


1
1
2022-10-01
12

2
1
2022-10-03
0

3
1
2022-10-04
10

4
1
2022-10-06
11

5
1
2022-10-07
10

6
2
2022-10-01
1

7
2
2022-10-02
0

8
2
2022-10-05
19

9
2
2022-10-10
18

13
3
2022-10-02
10

14
3
2022-10-03
0

15
3
2022-10-05
0

16
3
2022-10-06
12

_id	_category	_date	_value
1	1	2022-10-01	12
2	1	2022-10-03	0
3	1	2022-10-04	10
4	1	2022-10-06	11
5	1	2022-10-07	10
6	2	2022-10-01	1
7	2	2022-10-02	0
8	2	2022-10-05	19
9	2	2022-10-10	18
13	3	2022-10-02	10
14	3	2022-10-03	0
15	3	2022-10-05	0
16	3	2022-10-06	12