Average Watch Time
What is the average watch time in seconds for each video?
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Video Analytics is using average watch time per video as a proxy for content quality in the recommendation engine.
Return video_id, avg_watch_time in the declared deterministic order.
Return
- video_id
- avg_watch_time
Constraints
- Return videos with at least one view
- Round average seconds to two decimals
- Order by video ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
video_views
view_idINTEGERuser_idINTEGERvideo_idINTEGERview_dateDATEwatch_timeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Watch seconds are stored on individual view facts.
Hint 2
Compute the mean separately for each video.
Hint 3
Round the metric and order by the video key.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT video_id, ROUND(AVG(watch_time), 2) AS avg_watch_time FROM video_views GROUP BY video_id ORDER BY video_idWhy this works
video_id 7 (Machine Learning Basics) has 3 views at 720, 800, 780 seconds = avg 766.67. video_id 16 has 2 views at 750 and 800 = avg 775. Single-table aggregation — no JOIN needed since we only need video_id not title.
Success check
Returns the complete deterministic result for average watch time
Expected result
Use this output to verify values, aliases, ordering, and row count.
| video_id | avg_watch_time |
|---|---|
| 1 | 570 |
| 2 | 360 |
| 3 | 503.33 |
| 4 | 295 |
| 5 | 325 |
| 6 | 380 |
| 7 | 766.67 |
| 8 | 520 |
| 9 | 440 |
| 10 | 650 |
Previewing 10 of 20 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Netflix
Independent Netflix-style streaming, subscription, catalog, ratings, and engagement SQL practice.
Return to the complete interview preparation experience.