Calls between cities and inside

External_and_Internal_calls.cypher

// clean up
MATCH (:City)-[cw:CONNECT_WITH]-(:City)
DELETE cw

// external calls - total
MATCH (c1:City)<-[:FROM]-(:Person)-[]->(c:Call)<-[]-(:Person)-[:FROM]->(c2:City)
WHERE id(c1) > id(c2)
WITH c1.name as from, c2.name as to, count(c) as calls
RETURN sum(calls) as s

// external calls - group by
MATCH (c1:City)<-[:FROM]-(:Person)-[]->(c:Call)<-[]-(:Person)-[:FROM]->(c2:City)
WHERE id(c1) > id(c2)
RETURN c1.name as from, c2.name as to, count(c) as calls

// external calls - set as relation with COUNT OF CALLS and DURATION
MATCH (c1:City)<-[:FROM]-(:Person)-[]->(c:Call)<-[]-(:Person)-[:FROM]->(c2:City)
WHERE id(c1) > id(c2)
WITH c1, c2, count(c) as calls, sum(c.duration) as duration
MERGE (c1)-[:CONNECT_WITH { calls: calls, duration: duration }]->(c2);

// internal calls - total
MATCH (c1:City)<-[:FROM]-(p1:Person)-[]->(c:Call)<-[]-(p2:Person)-[:FROM]->(c2:City)
WHERE id(c1) = id(c2) AND id(p1) > id(p2)
WITH c1.name as from, c2.name as to, count(c) as calls
RETURN sum(calls) as s

// internal calls - group by
MATCH (c1:City)<-[:FROM]-(p1:Person)-[]->(c:Call)<-[]-(p2:Person)-[:FROM]->(c2:City)
WHERE id(c1) = id(c2) AND id(p1) > id(p2)
RETURN c1.name as from, c2.name as to, count(c) as calls

// internal calls - set as property
MATCH (c1:City)<-[:FROM]-(p1:Person)-[]->(c:Call)<-[]-(p2:Person)-[:FROM]->(c2:City)
WHERE id(c1) = id(c2) AND id(p1) > id(p2)
WITH c1, count(c) as calls
SET c1.calls = calls