How to get the next non-zero value in table partitioned by id?

October 20, 2022

Here is a subset of my table:

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

Where there are 0s in the value field, i would like to replace them with the non-zero value that occurs after the sequence of 0s are over, partitioned by id.

I have tried to use LAG but im really struggling as it takes the value above the current value in the table.

Any help will be appreciated.

Transformed table to look like

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

>Solution :

We group every 0 with the first value after it that’s not 0 and then we use max() over() to replace the 0s in the group.

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