question

renuka avatar image
renuka asked

To write an sql query

I want to write an sql query for the below: 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. The table details are as follows: Movie ( mID, title, year, director ) English: There is a movie with ID number mID, a title, a release year, and a director. Reviewer ( rID, name ) English: The reviewer with ID number rID has a certain name. Rating ( rID, mID, stars, ratingDate ) English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate. Please help
t-sqljoins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gopi Muluka avatar image
Gopi Muluka answered
Create Schema and data CREATE TABLE Movie ( mID INT, title SYSNAME, year INT, director SYSNAME) --English: There is a movie with ID number mID, a title, a release year, and a director. CREATE TABLE Reviewer ( rID INT, name SYSNAME ) --English: The reviewer with ID number rID has a certain name. CREATE TABLE Rating ( rID INT, mID INT, stars INT, ratingDate DATETIME ) INSERT Movie SELECT 1,'Terminator 2',1991, 'James Cameron' UNION SELECT 2, 'Titanic',1997, 'James Cameron' UNION SELECT 3, 'Avatar',2010,'James Cameron' UNION SELECT 4,'Pretty Woman',1990,'Garry Marshall' UNION SELECT 5,'Pretty Woman',1990,'Garry Marshall' UNION SELECT 6,'Godzilla',1998,'Roland Emmerich' UNION SELECT 7,'Strangers on a Train ',1951,' Alfred Hitchcock ' INSERT Reviewer SELECT 1,'Gopi' UNION SELECT 2, 'Fred' UNION SELECT 3,'Ricky' UNION SELECT 4, 'Michael Tracy' INSERT Rating SELECT 1,7,4,'12/12/2010' UNION SELECT 1,1,4,'12/12/2010' UNION SELECT 1,2,4,'12/12/2010' UNION SELECT 1,6,2,'12/12/2010' UNION SELECT 2,7,4,'12/12/2010' UNION SELECT 2,1,5,'12/31/2010' UNION SELECT 3,1,5,'12/12/2010' UNION SELECT 3,4,5,'12/12/2010' UNION SELECT 3,6,1,'12/12/2010' UNION SELECT 1,7,5,'02/12/2011' UNION SELECT 2,1,2,'02/12/2011' UNION SELECT 3,4,3,'02/12/2011' UNION SELECT 4,6,1,'12/12/2010' UNION SELECT 4,1,3,'12/12/2010' UNION SELECT 4,1,4,'02/01/2011' Query WITH Rating_CTE(rID,mID,Stars,RatingDate,RowNumber) AS ( SELECT A.rID,A.mID,B.Stars,B.ratingDate,B.Row_Num FROM ( SELECT rID,mID FROM Rating GROUP BY rID,mID HAVING COUNT(1)>1 ) A INNER JOIN ( SELECT rID,mID,Stars,RatingDate,ROW_NUMBER() OVER (PARTITION BY rID,mID ORDER BY RatingDate DESC) AS Row_Num FROM Rating )B ON A.rID=B.rID AND A.mID=B.mID ) SELECT R.Name, M.Title from Rating_CTE A inner join Rating_CTE B on a.rId=B.rID and A.mID=B.mID and a.RowNumber<>B.RowNumber and a.Stars>b.stars and a.RowNumber =1 INNER JOIN Reviewer R on A.rID=R.rID INNER JOIN Movie M on A.mID=M.mID
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Using the build script from @Gopi Muluka, (but changing all tables to be temporary objects for the example) I wouldnt choose to use a CTE and simply do it with a self join: SELECT [r].[stars] AS [First review], [r].[ratingDate] [First review date], [r3].[name] AS [Reviewer], [m].[title] AS [Film], [m].[year] AS [Year], [m].[director] AS [Director], [r2].[stars] AS [Second review], [r2].[ratingDate] AS [Second review date] FROM [#Rating] AS r INNER JOIN [#Reviewer] AS r3 ON [r].[rID] = [r3].[rID] INNER JOIN [#Movie] AS m ON [r].[mID] = [m].[mID] INNER JOIN [#Rating] AS r2 ON [r].[mID] = [r2].[mID] AND [r].[rID] = [r2].[rID] WHERE [r2].[ratingDate] > [r].[ratingDate] AND [r2].[stars] > [r].[stars] writing the query this way is about half the cost of using the CTE method.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack - Simple and easily understandable query...
1 Like 1 ·
@sathishkumar - thanks. That's how all code should be, however, if it was the slower option then it wouldnt be what should be used.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.