CODEWITHSUNDEEP
mysqlsqlduplicates
rsfan23
rsfan23

Reputation: 71

SQL query for duplicate rows based on 2 columns

I have 3 tables movie, rating and reviewer

movie has 4 columns movieID, title, year, director

rating has 4 columns reviewerID, movieID, stars, ratingDate

reviewer has 2 columns reviewerID, name

How do I query reviewer who rated the same movie more than 1 time and gave it higher rating on the second review.

This is my attempt at query to find rows with duplicate values in 2 columns (meaning the movie has been rated by 1 reviewer more than once), and then somehow I need to query reviewer who gave higher stars on second review.

SELECT reviewer.name, movie.title, rating.stars, rating.ratingDate
FROM rating
INNER JOIN reviewer ON reviewer.rID = rating.rID
INNER JOIN movie ON movie.mID = rating.mID
WHERE rating.rID IN (SELECT rating.rID FROM rating GROUP BY rating.rID, rating.mID HAVING COUNT(*) > 1)
ORDER BY reviewer.name, rating.ratingDate;

movie table

movieID Title Year Director
101 Gone with the Wind 1939 Victor Fleming
102 Star Wars 1977 George Lucas
103 The Sound of Music 1965 Robert Wise
104 E.T. 1982 Steven Spielberg
105 Titanic 1997 James Cameron
106 Snow White 1937 null
107 Avatar 2009 James Cameron
108 Raiders of the Lost Ark 1981 Steven Spielberg

rating table

reviewerID movie ID Stars ratingDate
201 101 2 2011-01-22
201 101 4 2011-01-27
202 106 4 null
203 103 2 2011-01-20
203 108 4 2011-01-12
203 108 2 2011-01-30
204 101 3 2011-01-09
205 103 3 2011-01-27
205 104 2 2011-01-22
205 108 4 null
206 107 3 2011-01-15
206 106 5 2011-01-19
207 107 5 2011-01-20
208 104 3 2011-01-02

reviewer table

reviewerID Name
201 Sarah Martinez
202 Daniel Lewis
203 Brittany Harris
204 Mike Anderson
205 Chris Jackson
206 Elizabeth Thomas
207 James Cameron
208 Ashley White

Expected result

Reviewer Title
Sarah Martinez Gone with the Wind

EDIT: I am using MySQL version 8.0.29

Upvotes: 2

Views: 95

Answers (2)

D-Shih
D-Shih

Reputation: 46219

we don't need to use where in with rating and join with rating

You can try to use lead window function to get the next start each reviewerID and movieID which represent duplicate rating (order by ratingDate)

then compare with your logic to find a newer start greater than older start.

SELECT DISTINCT r.Name,m.Title
FROM (
  SELECT reviewerID, 
         movieID,
         Stars,
         LEAD(Stars) OVER(PARTITION BY reviewerID, movieID ORDER BY ratingDate) n_start
  FROM rating 
) t1
INNER JOIN movie m ON t1.movieID = m.movieID
INNER JOIN reviewer r ON r.reviewerID = t1.reviewerID
WHERE Stars < t1.n_start

This sample data sqlfiddle provide by @ErgestBasha

Upvotes: 2

Ergest Basha
Ergest Basha

Reputation: 8973

Use:

select re.Name,mo.Title
FROM (
      select reviewerID,movieID,ratingDate,Stars
      from rating r 
      where exists (select 1 
              from rating r1 
              where r1.reviewerID=r.reviewerID 
              and r.movieID=r1.movieID 
              and r.ratingDate>r1.ratingDate
              and r.Stars>r1.Stars
       )) as t1
inner join movie mo on t1.movieID=mo.movieID
inner join reviewer re on t1.reviewerID=re.reviewerID

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0c5d850ee3393b054d9af4c4ac241d96

The key part is the EXISTS statement

where exists (select 1 
              from rating r1 
              where r1.reviewerID=r.reviewerID 
              and r.movieID=r1.movieID 
              and r.ratingDate>r1.ratingDate
              and r.Stars>r1.Stars

which will return only the results on which you have the same user more than one movie, the rating Stars are bigger than the previos one based on the ratingDate

Upvotes: 3

Related Questions