Count previous null values in redshift

Question

I have a table that is sorted by the 'date' column

date	value
1/1/2023	10
1/2/2023
1/3/2023
1/4/2023	20
1/5/2023
1/6/2023	40
1/7/2023	42
1/8/2023	3
1/9/2023
1/10/2023	1

I want to create a result like this. Where 'counts' column is the number of rows immediately above the current row that has null in 'value' column.

date	value	counts
1/1/2023	10	0
1/4/2023	20	2
1/6/2023	40	1
1/7/2023	42	0
1/8/2023	3	0
1/10/2023	1	1

Can this be done with a query in redshift?

Answer 1

You marked the question for window functions so you see this was likely to be solved.

Here's a solution (I changed date to dt and value to val as using key words as column names is generally not where you want to go):

with prec_null as (
select *, 
  count(case when val is null then 1 else null end)
    over (order by dt rows unbounded preceding) as cnt
from test )
select dt, val, cnt - coalesce(lag(cnt) over(order by dt), 0) as cnt
from prec_null 
where val is not null
order by dt;