A gist of the queries used in the Advanced Neo4j course

neo4j-advanced-subqueries.cql

//001 - How many movies has Tom Hanks acted in, how many has he directed?
MATCH (p:Person WHERE p.name = "Tom Hanks")-[:ACTED_IN]->(m:Movie)
WITH p, count(DISTINCT m) AS acts
MATCH (p)-[:DIRECTED]->(m:Movie)
WITH p, acts, count(DISTINCT m) AS directs
RETURN p.name AS name, acts, directs;


//002 - How many movies has each person acted in, how many have they directed?
MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
WITH p, count(DISTINCT m) AS acts
MATCH (p)-[:DIRECTED]->(m:Movie)
WITH p, acts, count(DISTINCT m) AS directs
RETURN p.name AS name, acts, directs;


//003 - Is there an option(al) that works?
MATCH (p:Person)
OPTIONAL MATCH (p)-[:ACTED_IN]->(m:Movie)
WITH p, count(DISTINCT m) AS acts
OPTIONAL MATCH (p)-[:DIRECTED]->(m:Movie)
WITH p, acts, count(DISTINCT m) AS directs
RETURN p.name AS name, acts, directs;


//004 - Comprehending Tom Hanks
MATCH (p:Person WHERE p.name = "Tom Hanks")
RETURN
   p.name AS name,
   size([(p)-[:ACTED_IN]->(m:Movie)|m.id]) AS acts,
   size([(p)-[:DIRECTED]->(m:Movie)|m.id]) AS directs;


//005 - Comprehending Tom Hanks better
MATCH (p:Person WHERE p.name = "Tom Hanks")
RETURN
  p.name AS name,
  size(apoc.coll.toSet([(p)-[:ACTED_IN]->(m:Movie)|m.id])) AS acts,
  size(apoc.coll.toSet([(p)-[:DIRECTED]->(m:Movie)|m.id])) AS directs;


//006 - Comprehending everyone better
MATCH (p:Person)
RETURN
  p.name AS name,
  size(apoc.coll.toSet([(p)-[:ACTED_IN]->(m:Movie)|m.id])) AS acts,
  size(apoc.coll.toSet([(p)-[:DIRECTED]->(m:Movie)|m.id])) AS directs;


//007 - Count in action
MATCH (p:Person WHERE p.name = "Tom Hanks")
RETURN
  p.name AS name,
  COUNT {MATCH (p)-[:ACTED_IN]->(m:Movie) RETURN DISTINCT m.id} AS acts,
  COUNT {MATCH (p)-[:DIRECTED]->(m:Movie) RETURN DISTINCT m.id} AS directs;


//008 - Count in action for all
MATCH (p:Person)
RETURN
  p.name AS name,
  COUNT {MATCH (p)-[:ACTED_IN]->(m:Movie) RETURN DISTINCT m.id} AS acts,
  COUNT {MATCH (p)-[:DIRECTED]->(m:Movie) RETURN DISTINCT m.id} AS directs;


//009 CALL Subquery
MATCH (p:Person)
CALL {
    WITH p
    MATCH (p)-[:ACTED_IN]->(m:Movie)
    RETURN sum(m.revenue - m.budget) AS totalProfit
}
RETURN p.name, totalProfit
ORDER BY totalProfit DESC 


//010
MATCH (p:Person)
CALL {
    WITH p
    MATCH (p)-[:ACTED_IN]->(m:Movie)
    RETURN sum(m.revenue - m.budget) AS totalActingProfit
}
CALL {
    WITH p
    MATCH (p)-[:DIRECTED]->(m:Movie)
    RETURN sum(m.revenue - m.budget) AS totalDirectingProfit
}
RETURN p.name, totalActingProfit, totalDirectingProfit
ORDER BY totalActingProfit DESC 


//011
MATCH 
    (p:Person)-[:ACTED_IN]->(actedIn:Movie),
    (p)-[:DIRECTED]->(directed:Movie)
RETURN 
    p.name,
    sum(actedIn.revenue - actedIn.budget) AS totalActingProfit,
    sum(directed.revenue - directed.budget) AS totalDirectingProfit

//012
UNWIND [1,2,3,4,5] AS i
CALL {
    WITH i
    CREATE (n:ExampleLabel {id: i})
} IN TRANSACTIONS;





//013
MATCH (n:ExampleLabel)
CALL {
    WITH n
    DETACH DELETE n
} IN TRANSACTIONS OF 2 ROWS;

//014
MATCH (p:Person)
WHERE EXISTS {
    (p)-[:DIRECTED]->(:Movie)
}
RETURN p.name AS director

//015
MATCH (p:Person)-[:DIRECTED]->(:Movie)
WHERE NOT EXISTS {
    (p)-[:ACTED_IN]->(:Movie)
} RETURN p LIMIT 10

//016 - Aggregation count
MATCH (p:Person WHERE p.name = "Tom Hanks")-[:ACTED_IN]->(m:Movie)
RETURN p.name AS name, count(DISTINCT m);

//017 - VERSUS Subquery COUNT
MATCH (p:Person WHERE p.name = "Tom Hanks")-[:ACTED_IN]->(m:Movie)
RETURN p.name AS name, COUNT{ (p)-[:ACTED_IN]->(m) };

//018 - Generic CALL {}
MATCH (p:Person)
CALL { WITH p
  MATCH (p)-[:ACTED_IN]->(m:Movie) 
  RETURN count(DISTINCT m) AS acts }
CALL { WITH p
  MATCH (p)-[:DIRECTED]->(m:Movie) 
  RETURN count(DISTINCT m) AS directs }
RETURN p.name AS name, acts, directs;