Unique Viewers per Title
Return movie_id and viewer_count (distinct users) for each movie, ordered by viewer_count descending then movie_id ascending.
- Joins
- Aggregation
- Sorting
- Distinct values
Challenge brief
Understand the request
Content Performance The content team uses unique viewer count (reach) as a key performance indicator for each title.
Return movie_id and viewer_count for every catalog movie in deterministic order.
Return
- movie_id
- viewer_count
Constraints
- Return every catalog movie, including titles with no views
- Count distinct identified viewers
- Report zero when no viewer matches
- Order by viewer count descending, then movie ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
movies
movie_idINTEGER
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
Begin with the movie catalog so unseen titles remain available.
Hint 2
Optionally match watch facts and count distinct non-NULL viewers.
Hint 3
Group at movie grain and order by the count and movie key.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT m.movie_id, COUNT(DISTINCT w.user_id) AS viewer_count FROM movies m LEFT JOIN watch_history w ON m.movie_id = w.movie_id GROUP BY m.movie_id ORDER BY viewer_count DESC, m.movie_idWhy this works
A catalog-preserving join keeps movies without watch facts. Counting the nullable viewer key returns zero for an unseen title while still deduplicating repeat viewers.
Success check
Every catalog movie appears; unseen titles have zero distinct viewers
Expected result
Use this output to verify values, aliases, ordering, and row count.
| movie_id | viewer_count |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 5 | 2 |
| 3 | 1 |
| 4 | 1 |
| 6 | 1 |
| 7 | 0 |
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.