Efficient Watch-Time Calculation | Dataford Interview Questions

What is an efficient way to compute how much time someone has watched a movie in Python?

For this SQL version, calculate valid watched time from playback session offsets, including open sessions and offsets outside the movie duration. Exclude sessions without valid viewer or movie matches and sessions with no positive duration.

Output

One row per viewer and movie with valid watch time.
Columns: viewer_id, viewer_name, movie_id, title, total_watched_seconds, and watch_percentage.
Sort by viewer_id, then movie_id, both ascending.

Schema

viewers

Column	Type	Description
viewer_idPK	INT	Unique viewer identifier
viewer_name	VARCHAR(100)	Viewer display name

movies

Column	Type	Description
movie_idPK	INT	Unique movie identifier
title	VARCHAR(200)	Movie title
duration_seconds	INT	Movie duration in seconds

watch_sessions

Column	Type	Description
session_idPK	INT	Unique playback session identifier
viewer_id	INT	Viewer associated with the session
movie_id	INT	Movie associated with the session
start_second	INT	Playback start offset in seconds
end_second	INT	Playback end offset in seconds, null when still open

Your question is Efficient Watch-Time Calculation. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem