Each entry in the movies table has:

user - user
movie - movie
rating - this user's rating for this movie

User 1 wants to receive recommendations (that is, a list of movies) from a user group of 2,3,4,5,6,7 .

Movies received should NOT be found among user movies 1 and
must have an average rating in this group of users not less than 3 . At the same time, films that were watched by less than 3 people from the group cannot be recommended.

Here is my option, but it does not reflect the last requirement:

SELECT t2.movie FROM movies t1 INNER JOIN movies t2 ON t1.user = 1 AND t2.user IN(2,3,4,5,6,7) AND t2.movie NOT IN ( SELECT movie FROM movies WHERE user = 1 ) GROUP BY(t2.movie) HAVING AVG(t2.rating)>=3

I tried to add this before grouping:

 AND COUNT(DISTINCT t2.user, t2.movie)>3

Writes that the wrong use of the group.
How to filter the results that occur in the group less than 3 times?

  • Make a Sql script which creates a database with records, then the problem will be solved faster :) - inzem77
  • 2
    The selection condition with the aggregate function cannot be used in the WHERE section, which is performed before grouping. Place the condition in the HAVING section. - Akina

1 answer 1

 SELECT t2.movie FROM movies t1 INNER JOIN movies t2 ON t1.user = 1 AND t2.user IN(2,3,4,5,6,7) AND t2.movie NOT IN ( SELECT movie FROM movies WHERE user = 1 ) GROUP BY(t2.movie) HAVING AVG(t2.rating)>=3 AND COUNT(DISTINCT t2.user) >= 3