0

I have a table of movies and a table of reviews

movies reviews

In my app, I want to show top 10 movies of any genre.

I clearly cannot sort movies just by rating since there are movies with only 1 5 star review, thus only irrelevant movies will be recommended to users.

Currently I receive from DB top 100 movies in this genre sorted by reviews, sort this list by rating on server and only then display top 10.

That kinda works but this solution is impractical in case of e.g. review bombing and moreover, the purpose of top 10 list is to recommend the most relevant movies.

My idea was to add relevance column into the movies table but I've got no clue how to count it:

  1. (amount of 5 star reviews * 5 ) + (amount of 4 star reviews * 4 ) and so on - no
  2. (amount of 5 star reviews * 1 ) + (amount of 4 star reviews * 0.8) + ... + (amount of 0 star reviews * 0.1) - no
  3. total amount of reviews / avgrating - no
  4. ((amount of 5 star reviews * 5 ) + (amount of 4 star reviews * 4 ) and so on) / amount of reviews total - mb, I'm not sure what about 0

Moreover, the rating in ratings is not a real number. User can give only 5, 4.5, 4 etc. score review. But what about the situation where users can rate movies like 5, 4.9, 4.8 ... 0.1?

So, how to perform this operation in better way?

[Upd] I think instead of division of smth. we should multiply averagerating and reviews from movies in order to count the relevance (averagerating and reviews are alredy automatically updated on each insert/delete/update). Also we should try to normalize the product.

In this situation movies with 100 reviews of 5 and averagerating of 5 won't beat up movies with averagerating 3.8 but with 57k reviews and also the problem of review bombing will be solved.

Can anyone prove my guess?

qwezxc789
  • 124
  • 1
  • 8
  • you can just add all ratings and divide it to the total number of ratings for that film, thats the average rating. And what do you mean by ratings are not real numbers? 4.5 looks like a real number to me – Etienne Jul 04 '21 at 12:58
  • @Etienne It seems to me that it won't work out properly. If you suggest to `select sum(rating) group by movie_id` and then divide it by `reviews` from `movies` (avgrating and reviews are automatically updated on each insert/delete/update), then you appear in a situation where a movie with reviews of ((50*5)+(40*4)+(60*3)+(50*2)+(10*1))/(50+40+60+50+10) will have the same relevance as a movie with reviews of ((500*5)+(400*4)+(600*3)+(500*2)+(100*1))/(500+400+600+500+100). what is not true. the first movie was rated 210 times meanwhile the second 2100 times – qwezxc789 Jul 04 '21 at 13:23
  • @Etienne what about real/not real, I have rather meant *the amount of possible reviews is discrete* So the set is finite. my bad. In case of 5, 4.9, 4.8 ... 0.1 it is also finite but hard to count for each member and also the set can be infinite - real number, what is nit my case but still this formula should work even for this case – qwezxc789 Jul 04 '21 at 13:25
  • @Etienne getting back to my first comment, such approach, doesn't solve the problem of zeros. All zeros will be ignored no matter how many users rated a movie as 0 – qwezxc789 Jul 04 '21 at 13:30
  • If you are asking how to define a reliable/relevant rating based on both the number of ratings and the rating value, then that is a statistics question and you need to ask a statistics forum. If you have defined the logic but want to know how to code it, then please add that logic to your question – NickW Jul 04 '21 at 14:31

1 Answers1

1

I agree with @NickW that this is more of a statistics question than a programming question, but I'll try to answer it, anyway.

If you want to account for both average rating and number of ratings, a straightforward method is to multiply the two. This gives you the sum of all ratings, but, as @qwezxc789 notes, this does not account for the number of zero ratings. Another strategy could be a linear combination of avgrating and reviews. Collinearity shouldn't be an issue because neither variable depends on the other. You could even play around with the linear coefficients to change the relative contribution of each variable. This solution easily generalizes to n independent variables.

Let wi be the weight of predictor i, 1 ≤ i ≤ n, w1 + ... + wn = 1 (or any other constant, but why not use 1?).

You can add this value as a new relevance column in the movies table using the following SQL. I use two equally weighted predictors: ratings and reviews.

ALTER TABLE movies ADD [relevance] AS
(SELECT 0.5 * avgrating + 0.5 * reviews
FROM movies)