Neo4j_create_satck_exchange_dbs.SQL

// dataset = ds

// Deleting all the nodes
MATCH (n)
DETACH DELETE n;

// Deleteing all the edges
MATCH ()-[r]->()
DELETE r;

// Loading the data and adding the nodes

LOAD CSV WITH HEADERS FROM 'file:///raw_data/ds/questions.csv' AS row FIELDTERMINATOR '\t'
    WITH toInteger(row["Id"]) AS questionId, toInteger(row["OwnerUserId"]) AS questionOwnerUserId, row["Body"] AS questionBody, row["Title"] AS questionTitle, split(row["Tags"], " ") AS questionTags
    MERGE (q:Question {questionId: questionId})
    SET q.questionBody = questionBody, q.questionOwnerUserId = questionOwnerUserId, q.questionTitle = questionTitle, q.questionTags = questionTags
    RETURN q;
   
LOAD CSV WITH HEADERS FROM 'file:///raw_data/ds/answers.csv' AS row FIELDTERMINATOR '\t'
    WITH toInteger(row["ParentId"]) AS parentQuestionId, toInteger(row["OwnerUserId"]) AS answerOwnerUserId, row["Body"] AS answerBody, toInteger(row["Id"]) AS answerId
    MERGE (a:Answer {answerId: answerId})
    SET a.answerBody = answerBody, a.answerOwnerUserId = answerOwnerUserId, a.parentQuestionId = parentQuestionId
    RETURN a;
 
LOAD CSV WITH HEADERS FROM 'file:///raw_data/ds/comments.csv' AS row FIELDTERMINATOR '\t'
    WITH toInteger(row["Id"]) AS commentId, toInteger(row["UserId"]) AS commentUserId, row["Text"] AS commentText, toInteger(row["PostId"]) AS parentPostId
    MERGE (c:Comment {commentId: commentId})
    SET c.commentUserId = commentUserId, c.commentText = commentText, c.parentPostId = parentPostId
    RETURN c;

LOAD CSV WITH HEADERS FROM 'file:///raw_data/ds/users.csv' AS row FIELDTERMINATOR '\t'
    WITH toInteger(row["Id"]) AS userId
    MERGE (u:User {userId: userId})
    RETURN u;
    
// Adding edges

MATCH
  (a:Answer),
  (q:Question)
  WHERE q.questionId = a.parentQuestionId
CREATE (a)-[r:ANSWERS]->(q)
RETURN r;

MATCH
  (c:Comment),
  (q:Question)
  WHERE q.questionId = c.parentPostId
CREATE (c)-[r:COMMENTS]->(q)
RETURN r;

MATCH
  (c:Comment),
  (a:Answer)
  WHERE a.answerId = c.parentPostId
CREATE (c)-[r:COMMENTS]->(a)
RETURN r;

MATCH
  (u:User),
  (q:Question)
  WHERE q.questionOwnerUserId = u.userId
CREATE (u)-[r:POSTS]->(q)
RETURN r;

MATCH
  (u:User),
  (a:Answer)
  WHERE a.answerOwnerUserId = u.userId
CREATE (u)-[r:POSTS]->(a)
RETURN r;

MATCH
  (u:User),
  (c:Comment)
  WHERE c.commentUserId = u.userId
CREATE (u)-[r:POSTS]->(c)
RETURN r;

// queries

// communication pattern 1: (u) --> (a) -- > (q) <-- (u)
MATCH (u1: User) -[:POSTS]-> (:Answer) -[:ANSWERS]->(q) <-[:POSTS]-(u2:User) return q.questionId, u1.userId, u2.userId 

// communication pattern 2: (u) --> (c) --> (q) <-- (u)
MATCH (u1: User) -[:POSTS]-> (:Comment) -[:COMMENTS]->(q) <-[:POSTS]-(u2:User) return q.questionId, u1.userId, u2.userId 

// communication pattern 3: (u) --> (c) --> (a) --> (q) and (u) --> (a)
MATCH (u1: User) -[:POSTS]-> (:Comment) -[:COMMENTS]->(a:Answer)-[:ANSWERS]-> (q:Question) WITH u1, a, q MATCH (u2:User)-[:POSTS]->(a)-[:ANSWERS]->(q) return q.questionId, u1.userId, u2.userId