# How to get the users who viewed a video on 95%?

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? Oct 2 '19 at 20:29
• I’m using MySql. Thanks.
– lexa
Oct 2 '19 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)? Oct 2 '19 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 '19 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 Oct 2 '19 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
``````

(fiddle)

• Wrong it seems. fiddle - user 5 have seen the whole video moments but he is absent in output. Oct 3 '19 at 5:31
• @Akina where's user 5 in the sample? The OP says it expects to see users 1 and 2. Oct 3 '19 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. Oct 3 '19 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. Oct 3 '19 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. Oct 3 '19 at 12:35