Average Rating per Title
Return title and avg_rating (rounded to 2 decimal places) for each movie, ordered by avg_rating descending.
- Joins
- Aggregation
- Numeric functions
- NULL handling
- Sorting
Challenge brief
Understand the request
Content Quality The content quality team monitors audience satisfaction through ratings and needs a per-title leaderboard.
Return title and avg_rating for every catalog movie in deterministic order.
Return
- title
- avg_rating
Constraints
- Return every catalog movie, including unrated titles
- Round rating averages to two decimals
- Place unrated titles after rated titles
- Order equal ratings by title
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
ratings
user_idINTEGERmovie_idINTEGERratingINTEGER
movies
movie_idINTEGERtitleVARCHAR(100)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Begin with the catalog and optionally match ratings.
Hint 2
Average rating values at movie grain; an unrated movie should remain NULL.
Hint 3
Place missing averages last and resolve equal ratings by title.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT m.title, ROUND(AVG(r.rating), 2) AS avg_rating FROM movies m LEFT JOIN ratings r ON m.movie_id = r.movie_id GROUP BY m.movie_id, m.title ORDER BY avg_rating IS NULL, avg_rating DESC, m.titleWhy this works
A catalog-preserving join keeps unrated titles. AVG ignores missing rating values, so an entirely unrated movie receives NULL rather than a misleading zero.
Success check
Every catalog movie appears; unrated titles retain a NULL average and sort last
Expected result
Use this output to verify values, aliases, ordering, and row count.
| title | avg_rating |
|---|---|
| The Crown | 5 |
| Dark | 4.5 |
| Stranger Things | 4.5 |
| Money Heist | 4 |
| Narcos | 4 |
| Extraction | 3 |
| Quiet Planet | NULL |
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.