How to find an actor having max earnings per movie in Neo4j db?

user_5632400 0
Viewed 42

I need to write a query to find the actor having max earnings per movie in Neo4j db.

I have written below query:

match (a:Person)-[role:ACTED_IN]->(m:Movie)
return m.title as Movie, max(role.earnings) as earnings

This gives me the max earning in a movie.

How to find the actor corresponding to that max earning?

cypher aggregation graph-databases neo4j aggregate-functions
2 Answers

Here are two alternatives.

Match again and look for the actor/actors that had max earnings:

match (a:Person)-[role:ACTED_IN]->(m:Movie)
with m, max(role.earnings) as earnings
match (a:Person)-[:ACTED_IN{earnings:earnings]->(m)
return m.title as Movie, earnings, a.name as actor

Sort and take first item in collection:

match (a:Person)-[role:ACTED_IN]->(m:Movie)
with m, role.earnings as earnings, a.name as actor order by earnings
return m.title as Movie, collect(earnings)[0] as earnings, collect(actor)[0] as actor

Some additional questions to consider:

  • How do you want to handle if two actors have the same earnings?
  • Can one actor play multiple roles in the same movie/have earnings per ACTED_IN relationship?
user_22593020

You can sort/collect the movie, earnings and actor by earnings in descending order. Then get the first item in the collection.

MATCH (a:Person)-[role:ACTED_IN]->(m:Movie)
WITH m, role.earnings as earnings, a ORDER BY earnings DESC
RETURN m.title as Movie, collect(earnings)[0] as earnings, collect(a.name)[0] as actor
user_73718930
Related