0

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

4
  • Hi, and welcome to the forum! Please give us your server name (Oracle, PostgreSQL) and version number... Aug 2 '21 at 20:10
  • 1
    Also consider posting sample data as text or in a fiddle
    – bbaird
    Aug 2 '21 at 21:34
  • Hello, it's Snowflake
    – r2k2
    Aug 3 '21 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). Aug 3 '21 at 19:51
2

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;
4
  • Charlieface, you are a lifesaver! This seems to be working properly for me. Thanks so much :)
    – r2k2
    Aug 3 '21 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! Aug 3 '21 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 Aug 3 '21 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! :-) Aug 3 '21 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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