CODEWITHSUNDEEP
mysqlsqldatejoingroup-by
danny
danny

Reputation: 417

MySQL: how to get average time spent by all users and single user

I have two tables every time when user comes I create new New user record table is following 

tbl_users:

---------------------------------------
id     | name     | created_at
---------------------------------------
1      | Danny    | 2020-04-20 12:47:37

and another table where they send messages

tbl_messages:
-------------------------------------------
id    | user_id   | message    | created_at
-------------------------------------------
1     | 1         | test mesh  | 2019-04-20 12:47:37

from user record created to last message created will be assumed as time spent by user. I am looking for a MySQL query where I can print single time spent and average time spent by all users? is it possible with MYSQL?

Upvotes: 0

Views: 578

Answers (1)

GMB
GMB

Reputation: 222602

You can join and aggregate twice:

select avg(max_diff) avg_diff_seconds
from (
    select max(timestampdiff(second, u.created_at, m.created_at)) max_diff
    from users u
    inner join messages m on m.user_id = u.id
    group by u.id, u.created_at
) t

The subquery computes the greatest difference between each user's creation time and the timestamp of their messages - this actually gives you the difference between the creation time and their latest message. Then, the outer query computes the average.

Upvotes: 2

Related Questions