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:

Count of null rows preceding each non-null value

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:

Count of null rows preceding each non-null value

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply