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:

enter image description here

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:

enter image description here

  • Hi, and welcome to the forum! Please give us your server name (Oracle, PostgreSQL) and version number...
    – Vérace
    Commented Aug 2, 2021 at 20:10
  • 1
    Also consider posting sample data as text or in a fiddle
    – user212533
    Commented Aug 2, 2021 at 21:34
  • Hello, it's Snowflake
    – r2k2
    Commented Aug 3, 2021 at 15:30
  • You might like to consider the advice on images here. Also, what happens if there are overlapping letters and dates? You should have some means of identifying the particular email that has been sent and when it was opened! Also, please provide tables as DDL and data as DML as suggested by @bbaird - also, to get someone's attention, you should use an @ sign followed by their handle (no spaces).
    – Vérace
    Commented Aug 3, 2021 at 19:51

1 Answer 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.

      COUNT(*) OVER (PARTITION BY Email, GroupId ORDER BY Send_Date)
    END AS CountTilOpen
    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;
  • Charlieface, you are a lifesaver! This seems to be working properly for me. Thanks so much :)
    – r2k2
    Commented Aug 3, 2021 at 18:31
  • @r2k2 - hmm... not sure about that! Check out the fiddle here - the last two snippets. To get your required answer, you have to put in NULLS FIRST in the SQL... The SQL in question fails on MySQL and SQL Server also - you'd have to resort to the tricks mentioned here to get the desired result. This is why you should always put your server version into your question!
    – Vérace
    Commented Aug 3, 2021 at 18:51
  • @Vérace Not convinced: the case you are worried about is where Send_date is the same, I would argue such a case is non-deterministic and either version is correct. There is no reason to believe that two rows with the same Send_date, and an Open_date of null in one of them, should necessarily be sorted in any particular order. Also I would have expected a real table to have a full date with time component and every row different, rendering the problem moot anyway Commented Aug 3, 2021 at 23:01
  • @Charlieface - and if you have 15 sent on the same day and one has an open_date also on that day, it is logical that that one should sort last? It affects the result and the NULLS FIRST sorting gives the answer the OP originally wanted? See my point about being able to identify individual emails in other comment (timestamp and email_id would be better - especially. for potential overlaps!) Really liked your solution though - gave it a +1. My point is perhaps a minor niggle, but a niggle nonetheless! :-)
    – Vérace
    Commented Aug 3, 2021 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.