Total Watch Time per User
Return user_id and total_minutes (sum of watch_minutes) for each user who has watched content, ordered by total_minutes descending.
- Aggregation
- Filtering
- Sorting
Challenge brief
Understand the request
Engagement Analytics The engagement team uses total watch time as a proxy for platform stickiness and needs a per-user summary.
Calculate total minutes watched by each user.
Return
- user_id
- total_minutes
Constraints
- Return only identified users with watch history
- Sum all watch minutes per user
- Order by total minutes descending, then user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
watch_history
user_idINTEGERmovie_idINTEGERwatch_minutesINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Watch facts contain both viewer ID and minutes.
Hint 2
Exclude anonymous activity before grouping by viewer.
Hint 3
Sum minutes and use the viewer key to stabilize ties.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT user_id, SUM(watch_minutes) AS total_minutes FROM watch_history WHERE user_id IS NOT NULL GROUP BY user_id ORDER BY total_minutes DESC, user_idWhy this works
SUM(watch_minutes) aggregated by user_id gives each user's total viewing time. ORDER BY total_minutes DESC ranks most-engaged users first.
Success check
Returns the complete deterministic result for total watch time per user
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | total_minutes |
|---|---|
| 1 | 800 |
| 2 | 650 |
| 3 | 600 |
| 6 | 550 |
| 8 | 480 |
| 5 | 400 |
| 4 | 350 |
| 7 | 200 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Return to the complete interview preparation experience.