Neo4J Movies Database: Which movie has the most number of actors in it?

[benni_]

Hi most of the queries/outputs tackle questions in relation to actor. Please help in formulating a query that outputs which movie has the most number of actors in it and which pair of actors have acted together in most number of movies?

[elaine_rosenber]

Hello @benni_ ,

At the sandbox site, there are two datasets for movies and actors. The movies dataset is smaller than the recommendations dataset.

For. you first question, a query to return the title of the movie with the greatest number of actors could be:

MATCH (m)-[ACTED_IN]-(a:Person)

WITH m, collect(a) AS actors

RETURN m.title, size(actors) as numActors ORDER BY size(actors) DESC LIMIT 1

Still looking into the best query to answer your second question.

Elaine

[benni_]

Hi, thank you so much for your response. I was trying put this query below and I get 12. Yours outputs 14. Why is there a discrepancy and which is the correct answer to the question?

MATCH (actor:Person)-[:ACTED_IN]-(movie:Movie) 
RETURN 
    movie.title, 
    COUNT(actor) as number_of_actors
ORDER BY number_of_actors DESC ;

[glilienfield]

@elaine_rosenber grouped by movie mode, while you grouped by movie title. I suspect there are multiple movie nodes with the same movie title. This would occur if they remade a movie and they had exactly the same title.

[glilienfield]

@elaine_rosenber solution works for your first query. As a note though, it only provides one of the actors with the highest number of movies. It would need some refactoring if you want all the actors that have the same max number of movies (if a tie exists).

The following works for your second query. I just ranked them and did not limit to 1 result, since there are multiple pairs of actors that have the same number of movies that that co-starred in.

match(n1:Person)-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(n2:Person)
with n1.name as actor_1, n2.name as actor_2, count(m) as movies
return *
order by movies desc

If you want to see an approach of getting all the top pairs of actors, the following demonstrates one:

match(n1:Person)-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(n2:Person)
with n1.name as actor_1, n2.name as actor_2, count(m) as movies
with max(movies) as max, collect({a1:actor_1, a2:actor_2, cnt: movies}) as stats
with [i in stats where i.cnt = max] as maxPairs
unwind maxPairs as stat
return stat.a1 as actor_1, stat.a2 as actor_2, stat.cnt as movies