Finding The Average Rating Of Each Of The Movies Sql

I'm currently taking the online stanford class on databases, If you could help me solve this sql problem I would greatly appreciate it. Sorry I'm a complete noob. Table Movie: mID

Solution 1:

SELECT distinct m.title, ar.avgRating FROM [Reviewer] rev JOIN [Rating] rate ON rev.rID = rate.rID JOIN [Movie] m ON rate.mID = m.mID JOIN ( SELECT mID FROM [Rating] GROUP BY mID HAVING COUNT(Distinct rID) > 1 ) m2 on m.mID = m2.mID JOIN ( SELECT mID, AVG(stars) as avgRating FROM [Rating] GROUP BY mID ) ar on m.mID = ar.mID ORDER BY avgRating desc