CODEWITHSUNDEEP
mysql
ehed
ehed

Reputation: 862

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

Given the table format below, where there is a separate row for the user clicking and completing a given event that share an event_id in common, it is possible to determine the average difference in time between click and complete in a 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            complete       2014-01-01 00:01:01
4        13            click          2014-01-01 00:02:01
5        12            complete       2014-01-01 00:03:01

Upvotes: 0

Views: 46

Answers (1)

radar
radar

Reputation: 13425

SELF JOIN and using TIMESTAMPDIFF, average can be calculated.

SELECT AVG(TIMESTAMPDIFF(SECOND,T2.event_create_date - T1.event_create_date))
FROM Table1 T1
JOIN Table1 T2
ON T1.event_type ='click'
and T2.event_type ='complete'
and T1.event_id = T2.event_id

Upvotes: 1

Related Questions