CODEWITHSUNDEEP
mysqlsql
Kaitlyn2004
Kaitlyn2004

Reputation: 3939

Calculate average days between events?

If I have a table that includes:

user_id | event_time

How can I calculate the average days between events? To get something like:

days_diff | count
        1 | 100
        2 | 90
        3 | 20

A user may have 1 day between events, but may also have 3 days between to subsequent events. How can I count them in both buckets?

Sample data (note in this case the DAY DIFF is 0/1 but this is just a small subset of data)

user_id | event_time
82770 2015-05-04 02:34:53
1 2015-05-04 08:45:53
82770 2015-05-04 20:38:24
82770 2015-05-04 20:38:24
82770 2015-05-04 20:38:24
1 2015-05-05 09:31:42
82770 2015-05-05 13:33:36
82770 2015-05-05 13:33:53
1 2015-05-06 09:53:59
1 2015-05-06 23:31:18
1 2015-05-06 23:31:35
1 2015-05-07 12:31:41
82770 2015-05-07 16:01:16

Upvotes: 1

Views: 140

Answers (3)

Zsuzsa
Zsuzsa

Reputation: 427

Here's a solution without using a temporary table:

select daybetweenevents as days_diff,
   count(daybetweenevents) as count
from (select t1.user_id,
    t1.event_time,
    datediff(day, t1.event_time, min(t2.event_time)) as daybetweenevents
    from yourtable t1
    inner join yourtable t2
       on t1.user_id = t2.user_id
    and t1.event_time < t2.event_time
    group by t1.user_id, t1.event_time) temp
group by daybetweenevents

Upvotes: 2

MartianCodeHound
MartianCodeHound

Reputation: 380

I went with a temporary table of sorted user events to make the correlation lookup easier and handle users with more than two events. This should get you the output you are asking for.

create table #tempOrderedUserEvents
(
id int identity (1,1),
userid int,
event_time datetime
)

insert into #tempOrderedUserEvents (userid, event_time)
select [user_id], event_time
from YourUserDataTable A
order by [user_id], event_time

select interval, count(*) as [count]
from
(
    select A.userid, datediff(day, A.event_time, B.event_time) as interval
    from #tempOrderedUserEvents A
    JOIN #tempOrderedUserEvents B on A.id+1 = B.id and A.userid = B.userid
) as Intervals
group by interval

drop table #tempOrderedUserEvents

Upvotes: 0

Matt
Matt

Reputation: 15061

Use DATEDIFF and a correlated sub query to get previous date.

SELECT user_id, event_time, 
DATEDIFF((SELECT MAX(event_time) 
          FROM yourtable 
          WHERE event_time < a.event_time), event_time) AS days_diff
FROM yourtable AS a

Upvotes: 1

Related Questions