# Count of null rows preceding each non-null value

## All we need is an easy explanation of the problem, so here it is.

I need to answer the following question:

What is the average number of email sends it take before a customer opens an email?
I know I need to count the number of rows between each Open_Date in order to get the number of email sends in between each open. I realize I need to add the row_number () function for that, but I can’t seem to get to the desired column in my second screenshot.

Something to keep in mind, I will need to get one average "CounttilOpens" per email address. In my example below, I have two different email addresses and their respective Send and Open dates and I need the query to function indepdently for each email address.

My data is structured as follows:

I need a query that help me get me a new column like this that restarts for each email address and for each gap of open dates:

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

This is a type of gaps-and-islands problem. There are many different solutions.

• Since `COUNT(SomeValue)` will only count non-null values, you can use a windowed count to calculate a grouping ID for each island.

• We subtract 1 for each row which is not null, in order to keep it as part of the previous group

• Then we use another windowed `COUNT` to get the final result, this time partitioning by the group ID also.

``````SELECT
Email,
Send_Date,
Open_Date,
CASE WHEN Open_Date IS NOT NULL THEN
COUNT(*) OVER (PARTITION BY Email, GroupId ORDER BY Send_Date)
END AS CountTilOpen
FROM (
SELECT *,
COUNT(Open_Date) OVER (PARTITION BY Email ORDER BY Send_Date)
- CASE WHEN Open_Date IS NULL THEN 0 ELSE 1 END
AS GroupId
FROM YourTable t
) t;
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂