SQL Server 2019

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

CREATE TABLE t
    (id int, date date, value int)
;
    
INSERT INTO t
    (id, date, value)
VALUES
    (1, '2022-01-01 00:00:00', 5),
    (1, '2022-02-02 00:00:00', 0),
    (1, '2022-03-01 00:00:00', 0),
    (1, '2022-04-02 00:00:00', 10),
    (2, '2022-01-04 00:00:00', 5),
    (2, '2022-02-04 00:00:00', 3),
    (2, '2022-03-04 00:00:00', 0),
    (2, '2022-04-04 00:00:00', 10)
;
8 rows affected

select *
from t


id
date
value


1
2022-01-01
5

1
2022-02-02
0

1
2022-03-01
0

1
2022-04-02
10

2
2022-01-04
5

2
2022-02-04
3

2
2022-03-04
0

2
2022-04-04
10


select   id
        ,date
        ,max(value) over(partition by id, grp) as value
from
(
select   *
        ,count(case when value != 0 then 1 end) over(partition by id order by date desc) as grp
from     t
) t
order by id, date


id
date
value


1
2022-01-01
5

1
2022-02-02
10

1
2022-03-01
10

1
2022-04-02
10

2
2022-01-04
5

2
2022-02-04
3

2
2022-03-04
10

2
2022-04-04
10


Warning: Null value is eliminated by an aggregate or other SET operation.

id	date	value
1	2022-01-01	5
1	2022-02-02	0
1	2022-03-01	0
1	2022-04-02	10
2	2022-01-04	5
2	2022-02-04	3
2	2022-03-04	0
2	2022-04-04	10