# How to perform a running count on null values that resets on non-null values

I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function

``````ROW_NUMBER() OVER (PARTITION BY ID, VAL ORDER BY VAL ROWS UNBOUNDED PRECEDING)
``````

But this is just giving me an incrementing number that does not reset or break upon seeing non-null values.

For example if I had a dataset like this:

``````id  | date  | val
----+-------+-------
1 |   1/1 | NULL
1 |   1/2 | NULL
1 |   1/3 | NULL
1 |   1/4 |  1
1 |   1/5 | NULL
1 |   1/6 | NULL
1 |   1/7 |  1
2 |   1/8 |  2
2 |   1/9 | NULL
2 |   1/1 | NULL
2 |   1/2 |  1
2 |   1/3 | NULL
2 |   1/4 |  0
2 |   1/5 | NULL
2 |   1/6 | NULL
``````

I would like the output to look like this:

``````id  | date  | val   | foo
----+-------+-------+-------
1 |   1/1 | NULL  |  1
1 |   1/2 | NULL  |  2
1 |   1/3 | NULL  |  3
1 |   1/4 |  1    |
1 |   1/5 | NULL  |  1
1 |   1/6 | NULL  |  2
1 |   1/7 |  1    |
2 |   1/8 |  2    |
2 |   1/9 | NULL  |  1
2 |   1/1 | NULL  |  2
2 |   1/2 |  1    |
2 |   1/3 | NULL  |  1
2 |   1/4 |  0    |
2 |   1/5 | NULL  |  1
2 |   1/6 | NULL  |  2
``````

But right now the foo column would just keep incrementing the nulls for each user.

• Which version of Redshift? Does it have window functions? Sep 3 '19 at 15:29
• Yes it does. PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.8187 Sep 3 '19 at 16:14