CODEWITHSUNDEEP
mysqlsql
brandrsn
brandrsn

Reputation: 3

Calculating average time between events in a flat table with SQL

In the sample MySQL database below, I'd like to analyse this data but my SQL skills let me down.

=========================================    
activity_type  email        activity_date
email_sent     [email protected]   2013-12-01
email_sent     [email protected]   2013-12-01
email_sent     [email protected]   2013-12-01
email_click    [email protected]   2013-12-04
email_click    [email protected]   2013-12-05
email_reply    [email protected]   2013-12-07
=========================================

Using SQL, how would you calculate the average length of time it took for an email to be clicked? (The answer is 3.5 days. Only emails a and b were clicked, and they were clicked 3 and 4 days later, respectively.)

Would your SQL query also allow us to calculate the average time it took for an email to be replied to, even though there is only on instance of this occurring?

Thanks in advance for helping out a super-n00b.

Upvotes: 0

Views: 1415

Answers (2)

mrras
mrras

Reputation: 462

First you have to join the lines of the sent event to the lines of the clicked event. then calculate the difference between the dates in days, then calculated the average.

SELECT AVG(e2.activity_date - e.activity_date) avg_gap
FROM email e
JOIN email e2 on (e.email = e2.email)
WHERE e.activity_type= 'email_sent'
AND e2.activity_type= 'email_click'

Upvotes: 0

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

SELECT avg(datediff(t2.activity_date, t1.activity_date))
FROM table t1
JOIN table t2 ON t1.email = t2.email
WHERE t1.activity_type = 'email_sent'
  AND t2.activity_type = 'email_click'

To calculate reply time just change the relevant t2.activity_type field.

Upvotes: 4

Related Questions