There is a table with viewed chunks.

video_id | user_id | start_second | end_second 
    1    |     1   |       0      |    10
    1    |     1   |       0      |    20
    1    |     2   |       1      |    21
    1    |     3   |       0      |    10
    1    |     4   |      15      |    21

For example, video length is 21 sec. So, I'm expecting to get user 1 and user 2, but not user 3 or user 4

  • what rdms are you using? – Joe W Oct 2 at 20:29
  • I’m using MySql. Thanks. – lexa Oct 2 at 20:32
  • You added a gaps-and-islands tag which implies that you're doing something with overlapping intervals. In your sample data, though, you could simply take the max(end_second) for each user and see if that is 20. Are your actual requirements more complicated than that? Could a user start at 15 seconds and watch to second 21? Would you want to count that (they got to 95%) or not (they didn't watch the first 15 seconds so they only really watched 6/21 ~= 29% of the video)? – Justin Cave Oct 2 at 20:47
  • @JustinCave Yes, a user could skip a chunk of video and watched from 15 to 21. But it mean that 29% of video was watched. – lexa Oct 2 at 20:57
  • I edited your question to add sample data for a user 4 that shows that you don't just want max(end_second). Please make sure that I haven't changed the intention of your question – Justin Cave Oct 2 at 21:00

Something like this, may be

select video_id, user_id, max(end_second - start_second)
from vid
group by video_id, user_id
having cast(max(end_second - start_second) as float)/21. > .95


  • Wrong it seems. fiddle - user 5 have seen the whole video moments but he is absent in output. – Akina Oct 3 at 5:31
  • @Akina where's user 5 in the sample? The OP says it expects to see users 1 and 2. – mustaccio Oct 3 at 10:41
  • OP gives EXAMPLE. I add one more user matched the condition - but your query rejected him. OP's sample data does not include all possible choices. – Akina Oct 3 at 11:30
  • @Akina We don't know that the OP is asking for someone who viewed 95% of the video in one session or across multiple sessions. You are making the assumption that as long as all times viewing the video add up to 95% that is what matters. However it is much more complicated to exclude skipped or duplicate ranges in the calculation. Considering that is not a scenario that the op provided I don't think they care about it. – Joe W Oct 3 at 12:29
  • in one session or across multiple sessions. For one session the solution is too trivial for to ask it about I think. We don't know that the OP is asking Subj: who viewed a video on 95%. User 5 in my addition have seen more than 95% of the video. exclude skipped or duplicate ranges Ranges may overlap - so all overlapped ranges must be joined into one range. – Akina Oct 3 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.