I have a table that looks like
(user_id, movie_id, start_timestamp, end_timestamp, reason_for_end)
reason_for_end == 'user_initiated'
reason_for_end == 'inactivity'
user_id movie_id start_timestamp end_timestamp reason_for_end
1 1 2012-11-18 05:53:36.0 2012-11-18 12:46:40.0 'inactivity'
1 2 2012-11-18 11:34:23.0 2012-12-18 13:21:57.0 'user_initiated'
How about that?
You should tweak date-time handling to your target database syntax. Also, I do not know how you wish to handle situations when user jumps between more films simultaneously - that's up to you ;-)
Otherwise, this should do the job:
SELECT movie1.user_id, count(*), sum(movie1.end_timestamp - movie2.start_timestamp) FROM movies movie1 LEFT JOIN movies movie2 ON movie1.user_id = movie2.user_id AND movie1.reason_for_end = 'inactivity' WHERE movie2.start_timestamp BETWEEN movie1.end_timestamp - '2hours'::interval AND movie1.end_timestamp AND movie1.user_id = 1 /* optional */ GROUP BY movie1.user_id