Haris Hasan Haris Hasan - 2 months ago 12
MySQL Question

How to calculate average session duration by login instance in MySQL?

I have two tables

users

id email
-------------------------------------------------------------
1 apple@apple.com
2 google@google.com


user_session_logs

id user_id start_timestamp end_timestamp
-------------------------------------------------------------
1 1 2016-01-01 12:00:00 2016-01-01 13:00:00
2 2 2016-01-01 14:00:00 2016-01-01 15:00:00
3 2 2016-02-03 06:00:00 2016-02-03 06:30:00
4 1 2016-03-03 05:00:00 2016-03-03 06:00:00
5 1 2016-04-03 07:00:00 2016-04-03 08:30:00


I want to calculate session duration distributions stratified by login instance (first login, second login, etc).
So given the data above, I want to get

results

login_instance average_session_duration_minutes
-------------------------------------------------------------
1 60 (this is the average of all user's 1st session duration)
2 45 (this is the average of all user's 2nd session duration)
3 90 (this is the average of all user's 3rd session duration)


Any ideas how this can be done in a mysql query?

Answer

This can be achieved using a subselect to count the login instances by counting the number of previous entries (i.e. with a lesser user_session_log id) for looking for the same user_id:

SELECT login_instance, AVG(session_duration_minutes) AS average_session_duration_minutes
FROM
(SELECT *,
        (SELECT COUNT(*)
         FROM user_session_logs usl2
         WHERE usl2.user_id = usl.user_id AND usl2.id <= usl.id) AS login_instance,
        TIMESTAMPDIFF(MINUTE, start_timestamp, end_timestamp) AS session_duration_minutes
 FROM user_session_logs usl) subq
GROUP BY login_instance;

See demo here: http://rextester.com/VVT36119

Comments