Cypher queries used for a demo presentation at liip

Cypher Query Examples

// ---------------------------------------------------------------
////  Loading data from CSV
// ---------------------------------------------------------------

CREATE CONSTRAINT ON (p:Person) ASSERT p.id IS UNIQUE;
CREATE CONSTRAINT ON (p:Person) ASSERT p.name IS UNIQUE;
LOAD CSV WITH HEADERS FROM "file:///Users/adri/Downloads/neo4j-community-2.1.0-M01/employees.csv" AS l
CREATE (p:Person { id: toInt(l.employee_id), name: l.employee_firstname + " " + l.employee_lastname});

CREATE CONSTRAINT ON (t:Tech) ASSERT t.id IS UNIQUE;
CREATE CONSTRAINT ON (t:Tech) ASSERT t.name IS UNIQUE;
LOAD CSV WITH HEADERS FROM "file:///Users/adri/Downloads/neo4j-community-2.1.0-M01/technologies.csv" AS l
CREATE (t:Tech { id: toInt(l.id), name: l.name });

LOAD CSV WITH HEADERS FROM "file:///Users/adri/Downloads/neo4j-community-2.1.0-M01/employee_technology.csv" AS l
MATCH (p:Person { name: toInt(l.employee_id)}),
      (t:Tech { id: toInt(l.technology_id)})
CREATE (p)-[:HAS_SKILL {expertise: toInt(l.expertise), feeling: toInt(l.feeling)}]->(t);


// ---------------------------------------------------------------
////  Delte all nodes and relationships
// ---------------------------------------------------------------

MATCH (n)
OPTIONAL MATCH (n)-[r]-()
DELETE n, r

// ---------------------------------------------------------------
////  Get all technologies a person is associated with
// ---------------------------------------------------------------

MATCH
    (me:Person {name: "Adrian Philipp"})-[:HAS_SKILL]->(tech)
RETURN 
    me,
    tech

// ---------------------------------------------------------------
////  Get tech two persons have in common
// ---------------------------------------------------------------

MATCH
    (me:Person {name: "Adrian Philipp"})-[t1:HAS_SKILL]->(tech),
    (collegue:Person {name: "Philipp Küng"})-[t2:HAS_SKILL]-(tech)
WHERE
    NOT me = collegue
RETURN
    me,
    collegue,
    tech

// -----------------------------------------------------------------
//// Find technologies which I might know too
// -----------------------------------------------------------------

MATCH
    (me:Person {name: "Adrian Philipp"})-[:HAS_SKILL]->(tech:Tech),
    (collegue:Person)-[:HAS_SKILL]->(tech:Tech),
    (collegue:Person)-[:HAS_SKILL]->(otherTech:Tech)
WHERE
    NOT me = collegue 
    AND NOT (me-[:HAS_SKILL]->otherTech)
RETURN
    otherTech.name,
    count(distinct collegue)
ORDER BY
    count(distinct collegue) DESC
LIMIT 10

// ---------------------------------------------------------------
//// Find similar users using cosine similiarity
//
// Fails for small numbers of technogies in common. Pretty useless.
// ---------------------------------------------------------------

MATCH
    (me:Person {name: "Adrian Philipp"})-[t1:HAS_SKILL]->(tech),
    (collegue:Person)-[t2:HAS_SKILL]-(tech)
WHERE
    NOT me = collegue
RETURN
    collegue.name,
    count(tech) as common_tech,
    sum(t1.expertise * t2.expertise) /
        (sqrt(sum(t1.expertise * t1.expertise)) * sqrt(sum(t2.expertise * t2.expertise))) as expertise_cossim,
    sum(t1.feeling * t2.feeling) /
        (sqrt(sum(t1.feeling * t1.feeling)) * sqrt(sum(t2.feeling * t2.feeling))) as feeling_cossim,
    collect(tech.name)
ORDER BY
    feeling_cossim DESC

// ---------------------------------------------------------------
//// Find similar users using cosine similiarity with count as weight
// ---------------------------------------------------------------

MATCH
    (me:Person {name: "Adrian Philipp"})-[t1:HAS_SKILL]->(tech),
    (collegue:Person)-[t2:HAS_SKILL]-(tech)
WHERE
    NOT me = collegue
RETURN
    collegue.name,
    count(tech) as common_tech,
    (1.0 - 1.0/count(tech)) * (
        sum(t1.expertise * t2.expertise) /
        (sqrt(sum(t1.expertise * t1.expertise)) * sqrt(sum(t2.expertise * t2.expertise)))
    ) as expertise_cossim,
    (1.0 - 1.0/count(tech)) * (
        sum(t1.feeling * t2.feeling) /
        (sqrt(sum(t1.feeling * t1.feeling)) * sqrt(sum(t2.feeling * t2.feeling)))
    ) as feeling_cossim,
    collect(tech.name)
ORDER BY
    feeling_cossim DESC

// ---------------------------------------------------------------
//// List the languages for a user and the count
// ---------------------------------------------------------------

MATCH
   (me:Person {name: "Philipp Küng"})-[r*2]->(l:Language)
RETURN
   distinct l.name,
   count(r) as r_count
ORDER BY
   r_count DESC