Top 5 Videos by Total Watch Time
Which 5 videos have accumulated the most total watch time across all viewers?
- Joins
- Aggregation
- Sorting
- Top-N
Challenge brief
Understand the request
Video Recommendations is identifying the most-watched content to boost in the recommendation feed and increase session time.
Return video_id, title, category, total_views, total_watch_time in the declared deterministic order.
Return
- video_id
- title
- category
- total_views
- total_watch_time
Constraints
- Rank videos by total watch seconds across all views
- Return exactly five videos
- Break watch-time ties by video ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
videos
video_idINTEGERtitleVARCHAR(200)categoryVARCHAR(50)
video_views
view_idINTEGERvideo_idINTEGERwatch_timeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Connect view facts to video labels and categories.
Hint 2
Aggregate count and watch seconds at video grain.
Hint 3
Order by total seconds with video ID as the tie-break before taking five.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT v.video_id, v.title, v.category, COUNT(vv.view_id) AS total_views, SUM(vv.watch_time) AS total_watch_time FROM videos v INNER JOIN video_views vv ON v.video_id = vv.video_id GROUP BY v.video_id, v.title, v.category ORDER BY total_watch_time DESC, v.video_id LIMIT 5Why this works
Machine Learning Basics (video 7) has 3 views at 720+800+780 = 2300s total. Python Tutorial has 4 views but a slightly lower total (2280s). Education dominates the top 5 — 4 of 5 are Education category.
Success check
Returns the complete deterministic result for top 5 videos by total watch time
Expected result
Use this output to verify values, aliases, ordering, and row count.
| video_id | title | category | total_views | total_watch_time |
|---|---|---|---|---|
| 7 | Machine Learning Basics | Education | 3 | 2300 |
| 1 | Python Tutorial for Beginners | Education | 4 | 2280 |
| 16 | Data Analytics Course | Education | 2 | 1550 |
| 3 | SQL Interview Questions | Education | 3 | 1510 |
| 11 | Web Development Tutorial | Education | 2 | 1400 |
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.