Content Performance Summary
Return movie_id, title, genre, total_watch_minutes, and avg_rating for each movie that has both watch history and ratings, ordered by total_watch_minutes descending.
- Joins
- Subqueries
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Executive Reporting Executives want a single content dashboard showing both viewing volume and audience sentiment for every title.
Return independently aggregated watch and rating metrics for movies represented in both fact sets.
Return
- movie_id
- title
- genre
- total_watch_minutes
- avg_rating
Constraints
- Aggregate watch minutes and rating averages independently at movie grain
- Include only movies represented in both activity sets
- Order by watch minutes descending, then movie ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
movies
movie_idINTEGERtitleVARCHAR(100)genreVARCHAR(50)
watch_history
movie_idINTEGERwatch_minutesINTEGER
ratings
movie_idINTEGERratingINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Watch history and ratings are independent one-to-many movie facts.
Hint 2
Reduce each fact source to one row per movie before combining them.
Hint 3
Join both summaries to catalog labels and order by true watch minutes.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH watch_totals AS (SELECT movie_id, SUM(watch_minutes) AS total_watch_minutes FROM watch_history GROUP BY movie_id), rating_avgs AS (SELECT movie_id, ROUND(AVG(rating), 2) AS avg_rating FROM ratings GROUP BY movie_id) SELECT m.movie_id, m.title, m.genre, w.total_watch_minutes, r.avg_rating FROM movies m INNER JOIN watch_totals w ON m.movie_id = w.movie_id INNER JOIN rating_avgs r ON m.movie_id = r.movie_id ORDER BY w.total_watch_minutes DESC, m.movie_idWhy this works
Watch history and ratings are each reduced to one row per movie before they are joined. This prevents every watch row from pairing with every rating row and multiplying watch totals.
Success check
Movie metrics reflect each fact source once, without watch-rating fanout
Expected result
Use this output to verify values, aliases, ordering, and row count.
| movie_id | title | genre | total_watch_minutes | avg_rating |
|---|---|---|---|---|
| 1 | Stranger Things | Sci-Fi | 1100 | 4.5 |
| 5 | Dark | Sci-Fi | 1030 | 4.5 |
| 2 | Money Heist | Crime | 750 | 4 |
| 3 | The Crown | Drama | 600 | 5 |
| 4 | Narcos | Crime | 350 | 4 |
| 6 | Extraction | Action | 230 | 3 |
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.