GraphAcademy

Cypher Aggregations

Understanding Aggregation in Cypher

Working with Lists

Aggregating Functions

Years Since Movie Reviewed

Years Since Movie Reviewed

Write a query to calculate the average number of years between the release of a movie and when it was reviewed.

  • Retrieve all movies reviewed by Angela Thompson.

  • Use datetime({epochseconds:r.timestamp}) for the datetime for when the movie was reviewed.

  • Use date(m.released) for the date when the movie was released.

  • Use duration.between() the two dates to create the duration.

  • Collect and REWIND to create the sum.

What is average time in years between the release date and when the movie was reviewed by Angela Thompson? (enter an integer number of years from the duration value)

Once you executed, enter the value below and click Check Answer.

  • ✓ 14

Hint

Your query should start with:

MATCH (u:User)-[r:RATED]-(m:Movie)
WHERE u.name = 'Angela Thompson'
WITH count(m) AS NumMovies, collect(duration.between(datetime({epochseconds:r.timestamp}), date(m.released))) AS ReviewPeriods

Then add code to UNWIND the list and sum the durations.

You will also calculate the average based on the total divided by NumMovies.

What is the average time in years between the release date and when the movie was reviewed by Angela Thompson? (enter an integer number of years from the duration value)

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:

cypher
MATCH (u:User)-[r:RATED]-(m:Movie)
WHERE u.name = 'Angela Thompson'
WITH count(m) AS NumMovies, collect(duration.between(datetime({epochseconds:r.timestamp}), date(m.released))) AS ReviewPeriods
UNWIND ReviewPeriods AS x
RETURN NumMovies, sum(x), sum(x)/NumMovies

What is the average time in years between the release date and when the movie was reviewed by Angela Thompson? (enter an integer number of years from the duration value)

Once you have entered the answer, click the Try Again button below to continue.

Summary

In this challenge, you wrote a query that used both duration() and sum() to return the average number of years between the time a movie was released and it was reviewed.

In the next lesson, you will learn how to calculate averages during aggregation.