sql - Average date diff on MySQL -

sql - Average date diff on MySQL -

i have table named 'game_logs' next structure:

id: integer user_id: integer game_id: integer game_event_type_id: integer timestamp: datetime

i have calculate average time diff between events of type "3" (game_event_type_id=3) records same user_id , game_id.

id, user_id, game_id, game_event_type_id, timestamp '1', '45', '92', '3', '2014-06-02 15:36:04' '2', '45', '92', '3', '2014-06-02 15:36:17' '3', '45', '92', '3', '2014-06-02 15:36:25' '4', '20', '91', '3', '2014-06-02 15:37:15'

i have been trying different things grouping game_id , user_id , calculating average @ same time seems challenging. left joining same table.

can help me? thanks!

you can average difference in seconds doing:

select user_id, game_id, (case when count(*) > 1 timestampdiff(second, min(timestamp), max(timestamp)) / (count(*) - 1) end) avgdiffseconds game_logs gl game_event_type_id = 3 grouping user_id, game_id;

the key thought average difference maximum minus minimum divided 1 less number of records.

mysql sql