Watch vs Rate Activity
Return user_id, movies_watched, and movies_rated for every user who has watched content. Use 0 for users who watched but never rated.
- Joins
- Subqueries
- Aggregation
- NULL handling
- Filtering
Challenge brief
Understand the request
Product — Ratings Feature The product team suspects most users watch without rating. They need a per-user comparison to measure the ratings funnel conversion.
Compare how many movies each user watched versus how many they rated.
Return
- user_id
- movies_watched
- movies_rated
Constraints
- Return identified users with watch history
- Count distinct watched and rated movies independently per user
- Use zero when a watcher has no ratings
- Order by user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
watch_history
user_idINTEGERmovie_idINTEGER
ratings
user_idINTEGERmovie_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Watched and rated movie counts come from separate fact sources.
Hint 2
Aggregate both independently per user and preserve the watcher population.
Hint 3
Zero-fill a missing rating count and order by user.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH user_watch AS (SELECT user_id, COUNT(DISTINCT movie_id) AS movies_watched FROM watch_history WHERE user_id IS NOT NULL GROUP BY user_id), user_rated AS (SELECT user_id, COUNT(DISTINCT movie_id) AS movies_rated FROM ratings GROUP BY user_id) SELECT uw.user_id, uw.movies_watched, COALESCE(ur.movies_rated, 0) AS movies_rated FROM user_watch uw LEFT JOIN user_rated ur ON uw.user_id = ur.user_id ORDER BY uw.user_idWhy this works
Two CTEs independently count distinct movies watched and rated per user. LEFT JOIN from the watch CTE ensures users who watched but never rated still appear — COALESCE converts the NULL to 0.
Success check
Returns the complete deterministic result for watch vs rate activity
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | movies_watched | movies_rated |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 2 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | 1 |
| 6 | 1 | 1 |
| 7 | 1 | 1 |
| 8 | 1 | 1 |
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.