Hands on Neo4j - Devoxx Morocco 2018 - solutions

gistfile1.txt

[
  {
    "instructions": "Find the number of movies",
    "solution": "MATCH (m: Movie) RETURN COUNT(m);"
  },
  {
    "instructions": "Find the number of action movies",
    "solution": "MATCH (m: Movie {genre: 'Action'}) RETURN COUNT(m);"
  },
  {
    "instructions": "Find the titles of the movies in which Vincent Cassel plays",
    "solution": "MATCH (m: Movie)<-[:ACTS_IN]-(a:Actor {name: 'Vincent Cassel'}) return m.title;"
  },
  {
    "instructions": "Find the titles of the movies directed by James Cameron",
    "solution": "MATCH (m: Movie)<-[:DIRECTED]-(:Director {name: 'James Cameron'}) return m.title;"
  },
  {
    "instructions": "Find the titles of the movies directed by James Cameron with Paramount Pictures studio",
    "solution": "MATCH (m: Movie {studio: 'Paramount Pictures'})<-[:DIRECTED]-(:Director {name: 'James Cameron'}) return m.title;"
  },
  {
    "instructions": "Find the names of the actors who played in the movies directed by James Cameron with Paramount Pictures studio",
    "solution": "MATCH (a: Actor)-[:ACTS_IN]->(m: Movie {studio: 'Paramount Pictures'})<-[:DIRECTED]-(:Director {name: 'James Cameron'}) return a.name;"
  },
  {
    "instructions": "Find the name of the actor who played both in 'Titanic' and 'The Lord of the Rings: The Return of the King'",
    "solution": "MATCH (a: Actor)-[:ACTS_IN]->(:Movie {title: 'Titanic'}), (a)-[:ACTS_IN]->(:Movie {title:'The Lord of the Rings: The Return of the King'}) return a.name;"
  },
  {
    "instructions": "Find the titles of the movies rated with 5 stars as well the names of the ones who rated it so",
    "solution": "MATCH (m: Movie)<-[:RATED {stars: 5}]-(p: User) return p.name as name, m.title as title;"
  },
  {
    "instructions": "Find the names of the directors who directed movies which name contains 'titanic'\nCypher protip: there is no LIKE keyword in Cypher, the =~ operator can be used instead, like `string =~ 'my-regex'`",
    "solution": "MATCH (m:Movie)<-[:DIRECTED]-(d:Director) WHERE LOWER(m.title) =~ '.*titanic.*' return d.name;"
  },
  {
    "instructions": "Find the titles of the movies which name starts with 'for' (in a case-insensitive way), optionally with their ratings (or null)",
    "solution": "MATCH (m:Movie) WHERE LOWER(m.title) =~ 'for.*' OPTIONAL MATCH (m)<-[r:RATED]-(:User)  RETURN m.title AS title, r.stars AS stars;"
  },
  {
    "instructions" : "Aggregation time!\nFind the titles of the movies, in ascending alphabetical order, that received at least 2 ratings.\nCypher pro-tip: COLLECT & WITH are your friends",
    "solution": "MATCH (m:Movie)<-[r:RATED]-(:User) WITH m, COLLECT(r) AS ratings WHERE LENGTH(ratings) > 1 RETURN m.title ORDER BY m.title ASC;"
  },
  {
    "instructions" : "Find the names of the last two directors (friendly tip to make your life easier: only 3 directors match in total), in ascending alphabetical, who directed at least two movies with ratings.",
    "solution": "MATCH (d:Director)-[:DIRECTED]->(m:Movie)<-[r:RATED]-(:User) WITH d, COLLECT(m.title) AS titles WHERE LENGTH(titles) > 1 RETURN d.name SKIP 1;"
  },
  {
    "instructions" : "Find the names of the actors included in the shortest path between the actors 50 Cent and Michael Graham Cox.\nPlease note: the result should include both of these actors.\nCypher protip: the functions SHORTESTPATH, NODES and UNWIND should be useful ;)",
    "solution": "MATCH p=SHORTESTPATH((a:Actor {name:'Michael Graham Cox'})-[*1..]-(f:Actor {name:'50 Cent'})) UNWIND NODES(p) AS n WITH n WHERE n:Actor RETURN n.name;"
  },
  {
    "instructions": "Let's write to the database now!.\nFrance Télévisions is furious: Patrick Sébastien is not referenced in the data!\nPatrick Sébastien is both a great actor and director. Let's add him to the graph with his name and the adequate labels.",
    "solution": "CREATE (:Actor:Director {name:'Patrick Sébastien'});"
  },
  {
    "instructions": "France Télévisions is still pissed off, none of Patrick Sébastien's movies are referenced!\nLet's add him again (this time, only his director activity matters) and let's add his best movie as well (as well as the fact that he directed it):\nT'aime, released in French in 1999 (only define the movie title, language and release timestamp: 922917600).",
    "solution": "CREATE (:Director {name:'Patrick Sébastien'})-[:DIRECTED]->(:Movie {title:\"T'aime\", language:'fr', releaseDate:922917600});"
  },
  {
    "instructions": "Strangely enough, the biggest Matrix fan ever and Neo4j Inc. CEO Emil Eifrem has not yet rated the movie 'The Matrix Revolutions'.\nLet's fix that, let's make Emil rate the movie with 3 stars and the following comment: \"Meh, liked the previous ones better\".",
    "solution": "MATCH (emil:User {name:'Emil Eifrem'}), (matrix:Movie {title:'The Matrix Revolutions'}) CREATE (emil)-[:RATED {stars:3, comment:'Meh, liked the previous ones better'}]->(matrix);"
  },
  {
    "instructions": "By the way, let's add Emil Eifrem profession to the graph. After, without him, no Neo4j and no workshop today!\nAdd the 'profession' attribute with the value 'CEO' to the relevant node.",
    "solution": "MATCH (emil:User {name:'Emil Eifrem'}) SET emil.profession = 'CEO';"
  },
  {
    "instructions": "The director union is furious: some ratings are below 5 stars!\nLet's fix this discretely and remove all the ratings below 5 stars if and if the rater's login contains an a (case-insensitive).",
    "solution": "MATCH (u:User)-[r:RATED]->(:Movie) WHERE r.stars < 5 AND LOWER(u.login) =~ '.*a.*' DELETE r;"
  },
  {
    "instructions": "The SEO expert Agnès Iho is asking you to remove all the comments which size is below 15 chars!\nSuch comments must be removed but the ratings must stay!",
    "solution": "MATCH (:User)-[r:RATED]->(:Movie) WHERE LENGTH(r.comment) < 15 REMOVE r.comment;"
  },
  {
    "instructions": "The software architect Walter Falls vous demande d'implémenter l'optimisation suivante: instead of computing it every time, please persist the number of comments per movie as a movie attribute. Let's call this attribute 'comment_count'.\nCypher pro-tip: COLLECT should help you!",
    "solution": "MATCH (m:Movie)<-[r:RATED]-(:User) WHERE EXISTS(r.comment) WITH m, COLLECT(r.comment) AS comments SET m.comment_count = LENGTH(comments);"
  },
  {
    "instructions": "The director union has changed its strategy.\nInstead of removing the ratings below 5 stars like before, let's rather flag such bad ratings with a new attribute named 'hidden' if their rater is an influencer ('influencer' means more than 1 friend in that case).",
    "solution": "MATCH (f:User)-[:FRIEND]-(u:User) WITH LENGTH(COLLECT(f)) AS friend_count,u WHERE friend_count >= 1 MATCH (u)-[r:RATED]->(:Movie) WHERE r.stars < 5 WITH COLLECT(r) AS ratings FOREACH(rating IN ratings | SET rating.hidden = true);"
  }
]