SQL query for duplicate rows based on 2 columns

Question

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

Could you provide some sample data and expect result? thanks — D-Shih
– D-Shih, Commented May 9, 2022 at 14:08

Ergest Basha · Accepted Answer · 2022-05-09 15:04:27Z

3

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

answered May 9, 2022 at 15:04

Ergest Basha

9,3164 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

D-Shih Over a year ago

The existe also work as well upvote

rsfan23 Over a year ago

Works like a charm, thank you for explaning the EXISTS statement too, that is something new for me. I accepted your answer.

D-Shih · Accepted Answer · 2022-05-09 15:15:15Z

2

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

edited May 9, 2022 at 15:15

answered May 9, 2022 at 14:48

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

2 Comments

Ergest Basha Over a year ago

You need to include ratingDate condition to return only reviewer who rated the same movie more than 1 time and gave it higher rating on the second review dbfiddle.uk/…

D-Shih Over a year ago

@ErgestBasha Thank you for your remind I missing the condition fixed it

Collectives™ on Stack Overflow

SQL query for duplicate rows based on 2 columns

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related