Danny David Leybzon Danny David Leybzon - 1 year ago 61
SQL Question

How can I figure out when a user is watching both a movie and another movie's credits?

I have a table that looks like

(user_id, movie_id, start_timestamp, end_timestamp, reason_for_end)
. Movies can either end because the user turns them off (
reason_for_end == 'user_initiated'
) or because the credits finish (
reason_for_end == 'inactivity'
). The credits always last exactly two hours.

I want to know the total number of times that a user started a new movie while another movie was in the credits phase.

Separately, I also want to know the total amount of time that a user spends watching a movie while the credits of another movie are still rolling.

Example given table:

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'

Example result table 1:

user_id times_new_started_while_old_in_credits
1 1

Example result table 2:

user_id total_overlap_in_seconds
1 4320

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download