Most Engaged Users
Which users have an average video watch time above the platform-wide average?
- Subqueries
- Aggregation
- HAVING
- Sorting
Challenge brief
Understand the request
Video Recommendations is identifying highly engaged viewers to build seed audiences for the recommendation model.
Return user_id in the declared deterministic order.
Return
- user_id
Constraints
- Compare each viewer average with the average across all view records
- Return only above-average viewers
- Order by user 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
Two grains are involved: a viewer average and the overall view-record average.
Hint 2
Apply the comparison after user-level aggregation.
Hint 3
Return only qualifying user identifiers in stable order.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT user_id FROM video_views GROUP BY user_id HAVING AVG(watch_time) > (SELECT AVG(watch_time) FROM video_views) ORDER BY user_idWhy this works
The scalar subquery computes the platform average once (~490s). HAVING then compares each user's personal average against that threshold. Users who only watched short videos (like user 5 with 280s and 290s) fall below average.
Success check
Returns the complete deterministic result for most engaged users
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id |
|---|
| 2 |
| 4 |
| 8 |
| 11 |
| 13 |
| 16 |
| 18 |
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.