I am going over the ne04j movie tutorial database and I want to output movie has the most number of actors in it?

Question

Which movie has the most number of actors in it? Which pair of actors have acted together in most number of movies?

Here's what i tried

MATCH (actor:Person)-[:ACTED_IN]-(movie:Movie) 
RETURN movie.name

I expect the movie name with a count of the number of actors in the movie

Start by doing a count and sort the result. Then get the topmost row using limit 1 — jose_bacoy, Nov 30 '22 at 11:34

score 0 · Answer 1 · answered Dec 01 '22 at 15:41

This will give you pairs of actors who have acted together in most number of movies. There are a lot of ties since 7 pairs have acted in 3 movies.

We get the max number of movies the actors acted together (3 movies). Then again get the pair of actors where they acted in 3 movies.

MATCH (p1:Person)-[:ACTED_IN]->(n:Movie)<-[:ACTED_IN]-(p2:Person) WHERE p1 > p2
WITH p1, p2, count(n) as movies 
WITH max(movies) as max_count ORDER by max_count DESC LIMIT 1
MATCH (p1:Person)-[:ACTED_IN]->(n:Movie)<-[:ACTED_IN]-(p2:Person) WHERE p1 > p2
WITH max_count, p1, p2, count(n) as movies where movies  = max_count
RETURN p1 as Actor1, p2 as Actor2

It should be easy enough for you to do the other question about;

Which movie has the most number of actors in it?

I am going over the ne04j movie tutorial database and I want to output movie has the most number of actors in it?

1 Answers1