I'm attending the free DB course at Stanford Online, and frankly one of the exercises gave me some trouble. I have a feeling that this should be horribly simple, so for a DBA, I'm obviously not very good with SQL.

We're working with a simplified scenario for rating movies.

For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.

Here's the schema:

Movie ( mID, title, year, director ) 
Reviewer ( rID, name ) 
Rating ( rID, mID, stars, ratingDate ) 

How should I go about this?

  • 2
    Rated the movie exactly twice and no more? Jan 22, 2013 at 20:31
  • Yes, that's how I understand the text.
    – Roy
    Jan 22, 2013 at 20:46

4 Answers 4


You can self join to tables for this sort of criteria(at least, I can do this in SQL Server):

    rating rt
    JOIN movie m ON (rt.mid = m.mid)
    JOIN reviewer r on (rt.rid = r.rid)
    JOIN rating rt2 on (rt.mid = rt2.mid and rt.rid = rt2.rid)
    rt.ratingDate > rt2.ratingDate
    AND rt.stars >  rt2.stars;

Note that we're joing rating back to itself. This way we can use the WHERE clause to find rows in rating that are greater in both date and stars to other rows in rating.

You can review the full example I built in SQL Fiddle here.

If you want to add the condition that the reviewer made exactly these 2 reviews of the movie and no more, you can add:

    AND NOT EXISTS                                  --- not exists
        ( SELECT *
          FROM rating rt3                           --- another rating
          WHERE rt3.mid = m.mid                     --- for same movie
            AND rt3.rid = r.rid                     --- by same reviewer
            AND rt3.ratingDate <> rt2.ratingDate    --- on different date
            AND rt3.ratingDate <> rt1.ratingDate    --- than the first 2 reviews

This assumes that the Rating table has (rID, mID, ratingDate) as primary key.

  • The only issue I see with the first query is that if there are n reviews for a movie (by the same reviewer) and all have ascending (per date) ratings, the (movie,reviewer) combination will be shown n*(n-1)/2 times. Of course this can be taken care with SELECT DISTINCT Jan 22, 2013 at 21:35

Here is an analytic solution using the tables miracle173 built in SQL Fiddle. The analytics alleviate the need to query the Ratings table two three times.

SELECT re.Name, mo.Title FROM
   SELECT rID, mID, count(*) OVER (PARTITION BY rID, mID) MovieCount, stars
      , Lag(Stars) OVER (PARTITION BY rID, mID ORDER BY ratingDate) LagStars
   FROM Rating
) ra
JOIN Movie mo ON mo.mID = ra.mID
JOIN Reviewer re ON re.rID = ra.rID
WHERE MovieCount = 2 AND Stars > LagStars;


select name,title
from Rating Rating1 join Rating Rating2 on( 
and Rating1.rID=Rating2.rID
and Rating1.ratingDate Rating1.stars)
join Movie on (Movie.mID=Rating1.mID)
join Reviewer on (Reviewer.rID=Rating1.rID)
and (Rating1.rID, Rating1.mID) in (select rID,mID
from Rating
group by rID,mID
having count(*)=2)

You can check it at http://sqlfiddle.com/#!4/6957e/7.

It will only consider the films of a reviewer that he has rated exactly two times


A solution that combines parts from the previous answers is the following:

  rating rt1
  join rating rt2 on (rt1.rID = rt2.rID and rt1.mID = rt2.mID)
  join reviewer on (rt1.rID = reviewer.rID)
  join movie on (rt1.mID = movie.mID)
  rt1.ratingdate < rt2.ratingdate 
  and rt1.stars < rt2.stars
  and rt1.rID in (
    select rID
    from Rating r1
    group by r1.rID,r1.mID
    having count(*)=2);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.