Algorithm: Build a recommendation for movies you might like

Question

I need help designing an algorithm for recommendations on movies.

Every user in the system grades movies on a score between 1-100.

Tables consist of:

Table Movies
ID    Name    Year    Rating    Runtime

Table Con_MoviesToGenres
MovieID    GenreID

Table Con_MovieToUser
MovieID    UserID    Grade

I'm trying to build a SELECT query to return 5 most recommended movies for a specific movie.

Bearing in mind, I want to integrate in some way, similar genres, highest grades & movie Rating (so you want be recommended an R rated movie for a PG rated movie, unless it's really recommended in every other aspect). Also, if movie matches more than one genre, it will increase its recommendation ratio.

Bonus: If a user gives a low grade to a movie -> it will lose recommendation ratio.

Update: I meant for one user and one title. Whenever a user enters a "movie page" - he will get recommendations for other movies he might like.

Answer 1

This has to be done on an atomic level: calculate recommendations for one title OR user at a time.

There is no way you can fit all the details in a SQL query. This has to be done is real code.

Answer 2

Might not be relevant to SQL, but if you like python, there is some tutorial on this topic in a book called Collective Intelligence

Answer 3

If User A and User B have seen 10 movies in common, and there is a high positive correlation between their ratings (implying they both have similar opinions about movies), you could then take a movie which User B has given a high rating to and recommend it to User A.

To do something like this, maybe you could precompute an extra table which maps User X and User Y to the number of movies they have seen in common and the Pearson's correlation between their ratings

When a user asks for a recommendation you could use this table to find a highly correlated user, and then recommend something he has seen and liked which this person hasn't

For situations when a user doesn't have enough common users with anybody else, you could fall back to recommending the highest rated movie overall which the user hasn't seen

Answer 4

You might want to check out the NetFlix competition. Found an article about it here. May at least give you some good ideas...

Answer 5

You are late the contest is over.

But its fun.

You will have to look into some fancy math (oh I love all this)

1. Linear Algebra
2. Statistics
3. Artificial Intelligence
4. Graph Theory

Articles:

1. Item-based Collaborative Filtering Recommendation Algorithms (1424 citations)
2. TANGENT: A Novel, “Surprise-me”, Recommendation Algorithm
3. What is a Good Recommendation Algorithm? - HackerNews
4. Tony Phillips' Take on Math in the Media
5. We Recommend a Singular Value Decomposition
6. Netflix prize tribute: Recommendation algorithm in Python
7. Studying Recommendation Algorithms by Graph Analysis