Figuring out average transaction duration with separate MYSQL rows for start and end? -

Figuring out average transaction duration with separate MYSQL rows for start and end? -

given table format below, there separate row user clicking , completing given event share event_id in common, possible determine average difference in time between click , finish in single query?

id event_id event_type event_create_date 1 11 click 2014-01-01 00:00:01 2 12 click 2014-01-01 00:00:02 3 11 finish 2014-01-01 00:01:01 4 13 click 2014-01-01 00:02:01 5 12 finish 2014-01-01 00:03:01

self join , using timestampdiff, average can calculated.

select avg(timestampdiff(second,t2.event_create_date - t1.event_create_date)) table1 t1 bring together table1 t2 on t1.event_type ='click' , t2.event_type ='complete' , t1.event_id = t2.event_id

mysql