SQL subtract previous row from record value when no ID exists

Question

I have a problem that is similar to the question posted here " How can I subtract a previous row in sql? " but is more complex. Some solutions use an incremented ID number, my data does not natively have this, but open adding one if needed (but would need help with query to do this). I am trying to find the average number of days between email-opens for EACH user. my data looks like this

**Email Address | Open Date |  CampaignName**

Email1@gmail     |  01/01/2013 | WelcomeCampaign
Email1@gmail     |  03/01/2013 | WelcomeCampaign
Email1@gmail     |  05/20/2013 | WelcomeCampaign
Email2@gmail     |  01/16/2013 | WelcomeCampaign
Email2@gmail     |  02/28/2013 | WelcomeCampaign
Email3@gmail     |  01/05/2013 | WelcomeCampaign
Email3@gmail     |  01/10/2013 | WelcomeCampaign
Email3@gmail     |  02/15/2013 | WelcomeCampaign
Email3@gmail     |  03/15/2013 | WelcomeCampaign
Email3@gmail     |  04/02/2013 | WelcomeCampaign

The need is to get the data like this, where each date is subtracted from the previous record if email address matches

**Email Address | Open Date |  CampaignName | DaysBetween**

Email1@gmail     |  01/01/2013 | WelcomeCampaign | NA
Email1@gmail     |  03/01/2013 | WelcomeCampaign | 60
Email1@gmail     |  05/20/2013 | WelcomeCampaign | 80
Email2@gmail     |  01/16/2013 | WelcomeCampaign | NA
Email2@gmail     |  02/28/2013 | WelcomeCampaign | 42
Email3@gmail     |  01/05/2013 | WelcomeCampaign | NA
Email3@gmail     |  01/10/2013 | WelcomeCampaign | 5
Email3@gmail     |  02/15/2013 | WelcomeCampaign | 35
Email3@gmail     |  03/15/2013 | WelcomeCampaign | 30
Email3@gmail     |  04/02/2013 | WelcomeCampaign | 17

I then plan to average for each user (email2@gmail would be 70 days)

I think you mean the average for email1@gmail would be 70 days. The average for email2@gmail looks like 42 days. — Gordon Linoff, Mar 14 '14 at 20:41

Hart CO · Answer 1 · 2014-03-14T20:33:46.933

The method to do this varies by database, which you haven't specified, but if LAG() is available, this is very easy:

SELECT *,DATEDIFF(day,OpenDate,LAG(OpenDate) OVER(PARTITION BY EmailAddress ORDER BY OpenDate)) As DaysBetween
FROM Table1

If it's not available you'll do an offset self-join to achieve the same:

WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY EmailAddress ORDER BY OpenDate) AS RN
             FROM Table1)
SELECT a.*,DATEDIFF(day,a.OpenDate,b.OpenDate) AS DaysBetween
FROM cte a
LEFT JOIN cte b
  ON a.EmailAddress = b.EmailAddress
  AND a.RN = b.RN+1

Demo of both: SQL Fiddle

One of the above will work in most dbms (syntax differences of course), but not in MySQL.

score 0 · Accepted Answer · answered Mar 14 '14 at 20:40

If you want to get the average number of days between emails, there is a much easier way.

select EmailAddress, 
       (case when count(*) > 1 then (max(OpenDate) - Min(OpenDate)) / (count(*) - 1)
        end) as AvgTimeBetweenEmails
from table t
group by EmailAddress;

The average duration is the maximum open date minus the minimum open date divided by the number of emails minus 1.

As for the answer to your question, you want to use the lag() function, if your database supports it.

SQL subtract previous row from record value when no ID exists

2 Answers2