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 ReviewPeriodsThen 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:
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)/NumMoviesWhat 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.