This document provides some model answers to help supervisors. Every exercise is designed so that a correct answer produces a unique output. But of course there are many ways to write a correct query.
Recall from the tutorial that this query:
match (p1:Person)-[:ACTS_IN]->(:Movie)<-[:ACTS_IN]-(p2:Person)
where p1.name = 'Lawrence, Jennifer (III)'
and p2.name <> 'Lawrence, Jennifer (III)'
return count(*) as total ;
returns a total of 331 (for the small database), while this query:
match (p1:Person)-[:ACTS_IN]->(:Movie)<-[:ACTS_IN]-(p2:Person)
where p1.name = 'Lawrence, Jennifer (III)'
and p2.name <> 'Lawrence, Jennifer (III)'
return count(distinct p2) as total ;
returns a total of 321. That is because some co-stars are counted twice in the first query and only once (correctly) in the second query. Who are the co-stars counted multiple times and how many movies did they act in with Jennifer Lawrence? Let's use Cypher to find out!
Complete the following Cypher query so that it returns rows name, total where the name column is the name of an actor, and the total column indicates the number of movies in which they co-starred with Jennifer Lawrence. Note, here we want the total to always be greater than 1. (HINT: Consider using the WITH construct.)
Solution notes:
match (p1:Person)-[:ACTS_IN]->(:Movie)<-[:ACTS_IN]-(p2:Person)
where p1.name = 'Lawrence, Jennifer (III)'
and p2.name <> 'Lawrence, Jennifer (III)'
with p2.name as name, count(*) as total
where total > 1
return name, total
order by name, total;
On the small data set this query produces the output:
+-----------------------------------+ | name | total | +-----------------------------------+ | "Birnbaum, Elias" | 2 | | "Cooper, Bradley (I)" | 2 | | "De Niro, Robert" | 2 | | "Eklund Jr., Dicky" | 2 | | "Falvo, Mark" | 2 | | "Herman, Paul (I)" | 2 | | "Meck, Patsy" | 2 | | "Russell, Matthew (VIII)" | 2 | | "Webb, Volieda" | 2 | | "Whigham, Shea" | 2 | +-----------------------------------+
In the tutorial we computed the distance between Jennifer Lawrence and Matt Damon (using the ACTS_IN relationship). The distance between two actors A and B is 1 if they co-starred in the same movie; the distance is 2 if there is some other actor X such that A co-starred with X on some movie and X co-starred with B on some other movie; and so on.
For this exercise we will compute the distance between all of our genres. The definition of distance is similar: for example, the distance between two genres is 1 if there is at least one movie that is associated with those two genres. We can use this distance as a measure of how similar two genres are.
We will use the HAS_GENRE relationship rather than the ACTS_IN relationship. Your query should output the distance between every genre and every other genre that exists in the database. Make sure your query enforces the constraint g1.genre < g2.genre so that each pair of genres is only listed once (if you swap the order of the two genres, the distance is still the same).
match (g:Genre)
with g.genre as genre1
YOUR-CODE-GOES-HERE
return distinct genre1, g2.genre as genre2, length(path)/2 as length
order by length desc, genre1, genre2;
Solution notes:
match (g:Genre)
with g.genre as genre1
match path = allshortestpaths( (g1:Genre)-[:HAS_GENRE*]-(g2:Genre) )
where g1.genre = genre1
and g1.genre < g2.genre
return distinct genre1, g2.genre as genre2, length(path)/2 as length
order by length desc, genre1, genre2;
On the small data set this query produces the output:
+----------------------------------------+ | genre1 | genre2 | length | +----------------------------------------+ | "Action" | "News" | 3 | | "Animation" | "News" | 3 | | "Biography" | "Musical" | 3 | | "Comedy" | "News" | 3 | | "Crime" | "Musical" | 3 | | "Family" | "News" | 3 | | "Fantasy" | "News" | 3 | | "History" | "Musical" | 3 | | "Horror" | "Musical" | 3 | | "Horror" | "News" | 3 | | "Musical" | "News" | 3 | | "Musical" | "Sport" | 3 | | "Musical" | "War" | 3 | | "Mystery" | "News" | 3 | | "News" | "Romance" | 3 | | "News" | "Sci-Fi" | 3 | | "News" | "Western" | 3 | | "Action" | "Biography" | 2 | | "Action" | "Documentary" | 2 | | "Action" | "History" | 2 | | "Action" | "Horror" | 2 | | "Action" | "Music" | 2 | | "Action" | "Musical" | 2 | | "Action" | "Mystery" | 2 | | "Action" | "Romance" | 2 | | "Action" | "Sport" | 2 | | "Action" | "War" | 2 | | "Action" | "Western" | 2 | | "Adventure" | "Biography" | 2 | | "Adventure" | "Crime" | 2 | | "Adventure" | "History" | 2 | | "Adventure" | "Horror" | 2 | | "Adventure" | "Music" | 2 | | "Adventure" | "News" | 2 | | "Adventure" | "Sport" | 2 | | "Adventure" | "War" | 2 | | "Animation" | "Crime" | 2 | | "Animation" | "Documentary" | 2 | | "Animation" | "History" | 2 | | "Animation" | "Horror" | 2 | | "Animation" | "Music" | 2 | | "Animation" | "Musical" | 2 | | "Animation" | "Mystery" | 2 | | "Animation" | "Romance" | 2 | | "Animation" | "Sport" | 2 | | "Animation" | "Thriller" | 2 | | "Animation" | "Western" | 2 | | "Biography" | "Comedy" | 2 | | "Biography" | "Family" | 2 | | "Biography" | "Fantasy" | 2 | | "Biography" | "Horror" | 2 | | "Biography" | "Mystery" | 2 | | "Biography" | "News" | 2 | | "Biography" | "Romance" | 2 | | "Biography" | "Sci-Fi" | 2 | | "Biography" | "Western" | 2 | | "Comedy" | "Crime" | 2 | | "Comedy" | "Documentary" | 2 | | "Comedy" | "History" | 2 | | "Comedy" | "Horror" | 2 | | "Comedy" | "Mystery" | 2 | | "Comedy" | "Sci-Fi" | 2 | | "Comedy" | "Sport" | 2 | | "Comedy" | "Thriller" | 2 | | "Comedy" | "War" | 2 | | "Comedy" | "Western" | 2 | | "Crime" | "Family" | 2 | | "Crime" | "Fantasy" | 2 | | "Crime" | "Horror" | 2 | | "Crime" | "Music" | 2 | | "Crime" | "Mystery" | 2 | | "Crime" | "News" | 2 | | "Crime" | "Romance" | 2 | | "Crime" | "Sport" | 2 | | "Crime" | "Western" | 2 | | "Documentary" | "Family" | 2 | | "Documentary" | "Fantasy" | 2 | | "Documentary" | "Horror" | 2 | | "Documentary" | "Musical" | 2 | | "Documentary" | "Mystery" | 2 | | "Documentary" | "Romance" | 2 | | "Documentary" | "Sci-Fi" | 2 | | "Documentary" | "Western" | 2 | | "Drama" | "Musical" | 2 | | "Drama" | "News" | 2 | | "Family" | "History" | 2 | | "Family" | "Horror" | 2 | | "Family" | "Music" | 2 | | "Family" | "Mystery" | 2 | | "Family" | "Romance" | 2 | | "Family" | "Sport" | 2 | | "Family" | "Thriller" | 2 | | "Family" | "War" | 2 | | "Family" | "Western" | 2 | | "Fantasy" | "History" | 2 | | "Fantasy" | "Music" | 2 | | "Fantasy" | "Musical" | 2 | | "Fantasy" | "Romance" | 2 | | "Fantasy" | "Sport" | 2 | | "Fantasy" | "Western" | 2 | | "History" | "Horror" | 2 | | "History" | "Music" | 2 | | "History" | "Mystery" | 2 | | "History" | "Romance" | 2 | | "History" | "Sci-Fi" | 2 | | "History" | "Sport" | 2 | | "History" | "Western" | 2 | | "Horror" | "Music" | 2 | | "Horror" | "Sci-Fi" | 2 | | "Horror" | "Sport" | 2 | | "Horror" | "War" | 2 | | "Horror" | "Western" | 2 | | "Music" | "Musical" | 2 | | "Music" | "Mystery" | 2 | | "Music" | "News" | 2 | | "Music" | "Sci-Fi" | 2 | | "Music" | "Sport" | 2 | | "Music" | "Thriller" | 2 | | "Music" | "War" | 2 | | "Music" | "Western" | 2 | | "Musical" | "Mystery" | 2 | | "Musical" | "Romance" | 2 | | "Musical" | "Sci-Fi" | 2 | | "Musical" | "Thriller" | 2 | | "Musical" | "Western" | 2 | | "Mystery" | "Romance" | 2 | | "Mystery" | "Sci-Fi" | 2 | | "Mystery" | "Sport" | 2 | | "Mystery" | "War" | 2 | | "Mystery" | "Western" | 2 | | "News" | "Sport" | 2 | | "News" | "Thriller" | 2 | | "News" | "War" | 2 | | "Romance" | "Sport" | 2 | | "Romance" | "Thriller" | 2 | | "Romance" | "War" | 2 | | "Romance" | "Western" | 2 | | "Sci-Fi" | "Sport" | 2 | | "Sci-Fi" | "War" | 2 | | "Sci-Fi" | "Western" | 2 | | "Sport" | "Thriller" | 2 | | "Sport" | "War" | 2 | | "Sport" | "Western" | 2 | | "Thriller" | "Western" | 2 | | "War" | "Western" | 2 | | "Action" | "Adventure" | 1 | | "Action" | "Animation" | 1 | | "Action" | "Comedy" | 1 | | "Action" | "Crime" | 1 | | "Action" | "Drama" | 1 | | "Action" | "Family" | 1 | | "Action" | "Fantasy" | 1 | | "Action" | "Sci-Fi" | 1 | | "Action" | "Thriller" | 1 | | "Adventure" | "Animation" | 1 | | "Adventure" | "Comedy" | 1 | | "Adventure" | "Documentary" | 1 | | "Adventure" | "Drama" | 1 | | "Adventure" | "Family" | 1 | | "Adventure" | "Fantasy" | 1 | | "Adventure" | "Musical" | 1 | | "Adventure" | "Mystery" | 1 | | "Adventure" | "Romance" | 1 | | "Adventure" | "Sci-Fi" | 1 | | "Adventure" | "Thriller" | 1 | | "Adventure" | "Western" | 1 | | "Animation" | "Biography" | 1 | | "Animation" | "Comedy" | 1 | | "Animation" | "Drama" | 1 | | "Animation" | "Family" | 1 | | "Animation" | "Fantasy" | 1 | | "Animation" | "Sci-Fi" | 1 | | "Animation" | "War" | 1 | | "Biography" | "Crime" | 1 | | "Biography" | "Documentary" | 1 | | "Biography" | "Drama" | 1 | | "Biography" | "History" | 1 | | "Biography" | "Music" | 1 | | "Biography" | "Sport" | 1 | | "Biography" | "Thriller" | 1 | | "Biography" | "War" | 1 | | "Comedy" | "Drama" | 1 | | "Comedy" | "Family" | 1 | | "Comedy" | "Fantasy" | 1 | | "Comedy" | "Music" | 1 | | "Comedy" | "Musical" | 1 | | "Comedy" | "Romance" | 1 | | "Crime" | "Documentary" | 1 | | "Crime" | "Drama" | 1 | | "Crime" | "History" | 1 | | "Crime" | "Sci-Fi" | 1 | | "Crime" | "Thriller" | 1 | | "Crime" | "War" | 1 | | "Documentary" | "Drama" | 1 | | "Documentary" | "History" | 1 | | "Documentary" | "Music" | 1 | | "Documentary" | "News" | 1 | | "Documentary" | "Sport" | 1 | | "Documentary" | "Thriller" | 1 | | "Documentary" | "War" | 1 | | "Drama" | "Family" | 1 | | "Drama" | "Fantasy" | 1 | | "Drama" | "History" | 1 | | "Drama" | "Horror" | 1 | | "Drama" | "Music" | 1 | | "Drama" | "Mystery" | 1 | | "Drama" | "Romance" | 1 | | "Drama" | "Sci-Fi" | 1 | | "Drama" | "Sport" | 1 | | "Drama" | "Thriller" | 1 | | "Drama" | "War" | 1 | | "Drama" | "Western" | 1 | | "Family" | "Fantasy" | 1 | | "Family" | "Musical" | 1 | | "Family" | "Sci-Fi" | 1 | | "Fantasy" | "Horror" | 1 | | "Fantasy" | "Mystery" | 1 | | "Fantasy" | "Sci-Fi" | 1 | | "Fantasy" | "Thriller" | 1 | | "Fantasy" | "War" | 1 | | "History" | "News" | 1 | | "History" | "Thriller" | 1 | | "History" | "War" | 1 | | "Horror" | "Mystery" | 1 | | "Horror" | "Romance" | 1 | | "Horror" | "Thriller" | 1 | | "Music" | "Romance" | 1 | | "Mystery" | "Thriller" | 1 | | "Romance" | "Sci-Fi" | 1 | | "Sci-Fi" | "Thriller" | 1 | | "Thriller" | "War" | 1 | +----------------------------------------+
Let's build a simple recommendation engine. Starting from a known movie that you liked, write a query to finds similar movies that you might also enjoy.
Let A be the movie that you liked. Some other movie B should be considered for recommendation if A and B have at least one genre in common, at least one keyword in common, and at least one actor in common. Furthermore, you can calculate a similarity score as follows: 1 point for every keyword that A and B have in common, and 10 points for every actor the movies have in common.
Write a query that produces recommendations for someone who liked Skyfall (2012). The results should give the title of each recommended movie and the similarity score, and be sorted by score:
Solution notes:
match
(liked:Movie)-[:HAS_KEYWORD]->(keyword:Keyword)<-[:HAS_KEYWORD]-(rec:Movie),
(liked)<-[:ACTS_IN]-(actor:Person)-[:ACTS_IN]->(rec),
(liked)-[:HAS_GENRE]->()<-[:HAS_GENRE]-(rec)
where liked.title = 'Skyfall (2012)'
with rec, count(distinct keyword) as common_keywords, count(distinct actor) as common_actors
return rec.title as title, 10*common_actors + common_keywords as score
order by score desc;
On the small data set this query produces the output:
+---------------------------------------------------------------+ | title | score | +---------------------------------------------------------------+ | "Star Wars: Episode VII - The Force Awakens (2015)" | 138 | | "The Dark Knight (2008)" | 121 | | "Casino Royale (2006)" | 102 | | "Mission: Impossible - Ghost Protocol (2011)" | 87 | | "Harry Potter and the Deathly Hallows: Part 2 (2011)" | 57 | | "The Bourne Ultimatum (2007)" | 53 | | "The Hurt Locker (2008)" | 47 | | "No Country for Old Men (2007)" | 40 | | "The Cabin in the Woods (2012)" | 32 | | "Argo (2012)" | 24 | +---------------------------------------------------------------+