Finding out time frame within one column

user_14617447 0
Viewed 37

We have an app that helps people find other basketball players to play with. I have been tasked to find out the average time between each joiner of a game For example, Game 2445, one user joined, then another, then another. I want to find out the time frame between each of these joiners.

The game_users table has the following format

game_id | user_id | active | created_at 

1234    |3455    |   1     | 2020-01-26 05:18:18

1234    |5673    |   1     |2020-02-16 04:16:15

1234    |2344    |   1     |2020-02-22 18:59:55

Created_at here refers to the time a user joined the game

How can I find out the average time it takes a user to join a game?

mysql-5.7 aggregate-functions mysql average sql
1 Answers

first you need to find out the time difference between two join, you can use window function LEAD() and TIMESTAMPDIFF() ( in my version I'm showing the difference in hours, you can change it to whatever make sense to you), then you can calculate the average using cte :

;with cte as (
SELECT  
  * 
  ,TIMESTAMPDIFF(HOUR, created_at ,LEAD(created_at) OVER (Partition by game_id order by created_at)) as TimeDifference
FROM game_users 
)

SELECT 
   game_id 
   , AVG(TimeDifference) as avgTimedifference
FROM cte
GROUP BY game_id
user_13674540
Related