Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Rated the movie exactly twice and no more? –  Martin Smith Jan 22 '13 at 20:31
    
Yes, that's how I understand the text. –  Roy Jan 22 '13 at 20:46
    
it would be useful if you implement tables with sample data at sqlfiddle.com –  miracle173 Jan 22 '13 at 20:48
    
Taking a look at sqlfiddle now, looks like a very handy tool –  Roy Jan 22 '13 at 21:17
add comment

3 Answers 3

up vote 4 down vote accepted

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

SELECT
     r.name,
     m.title
FROM
    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)
WHERE
    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.

share|improve this answer
    
This certainly seems to do the trick, well, except from the order of the fields selected, of course ;) Still trying to get my head around that third join. I guess this one is not limited to two matching reviews, but as there are no movies in the sample database with three ratings by the same reviewer, I suppose that may very well be outside the scope of the exercise. –  Roy Jan 22 '13 at 20:54
    
Yes, you could return 2 or more rows, IF they match the criteria of the WHERE clause. There are ways to try and narrow that down to only one record, but it gets a LOT more complex and requires you to tightly define the criteria for those other rows. –  Mike Fal Jan 22 '13 at 21:01
    
this will return data if there are three ratings for a film by one reviewer –  miracle173 Jan 22 '13 at 21:26
    
Aye, thats what I said, miracle173. I think it's probably outside the scope of the exercise. –  Roy Jan 22 '13 at 21:29
1  
@MikeFal I hope you don't mind the edit. This will ensure that there are exactly 2 reviews. –  ypercube Jan 22 '13 at 21:30
show 1 more comment

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;

http://sqlfiddle.com/#!4/6957e/16

share|improve this answer
add comment
select name,title
from Rating Rating1 join Rating Rating2 on( 
Rating1.mID=Rating2.mID 
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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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