Using WITH to pass on intermediate results
Write and execute a query to determine the highest average rating by a user for a Tom Hanks Movie. Use avg(r.rating) to aggregate the rating values for all movies that Tom Hanks acted in, where you use the pattern (m:Movie)←[r:RATED]-(:User).
Then answer this question:
What Tom Hanks movie had the highest average rating greater than 4?
Enter the title of the movie. (Note, the answer is case-sensitive).
-
✓ Captain Phillips
Hint
Find the Tom Hanks movies per this path: (p:Person)-[:ACTED_IN]→(m:Movie)←[r:RATED]-(:User)
Use WITH to calculate the average rating for all users per movie.
Filter the query to only process movies with an average rating that is greater than 4.
Return the movie with the highest rating.
What is the title of the average highest-rated movie? (case-sensitive)
Once you have entered the answer, click the Try Again button below to continue.
Solution
You can run the following query to find the answer:
MATCH (p:Person)-[:ACTED_IN]->(m:Movie)<-[r:RATED]-(:User)
WHERE p.name = 'Tom Hanks'
WITH m, avg(r.rating) AS avgRating
WHERE avgRating > 4
RETURN m.title AS Movie, avgRating AS `AverageRating`
ORDER BY avgRating DESCWhat is the title of the average highest-rated movie? (case-sensitive)
Once you have entered the answer, click the Try Again button below to continue.
Summary
In this challenge, you wrote a query to use a WITH clause to aggregate data.
In the next challenge, you will answer another question using this query.