StackOverflow Question 39789605, MySQL

Language: Layout:

DROP TABLE IF EXISTS user_session_logs;
    CREATE TABLE user_session_logs (id INT, user_id INT, start_timestamp DATETIME, end_timestamp DATETIME);
    INSERT INTO user_session_logs (id, user_id, start_timestamp, end_timestamp) VALUES(1, 1, '2016-01-01 12:00:00', '2016-01-01 13:00:00');
    INSERT INTO user_session_logs (id, user_id, start_timestamp, end_timestamp) VALUES(2, 2, '2016-01-01 14:00:00', '2016-01-01 15:00:00');
    INSERT INTO user_session_logs (id, user_id, start_timestamp, end_timestamp) VALUES(3, 2, '2016-02-03 06:00:00', '2016-02-03 06:30:00');
    INSERT INTO user_session_logs (id, user_id, start_timestamp, end_timestamp) VALUES(4, 1, '2016-03-03 05:00:00', '2016-03-03 06:00:00');
    INSERT INTO user_session_logs (id, user_id, start_timestamp, end_timestamp) VALUES(5, 1, '2016-04-03 07:00:00', '2016-04-03 08:30:00');

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;


absolute service time: 0,4 sec