// Execute these queries to populate your graph // PART1 : Creating Nodes (Vertices)
CREATE (us1:User {name:"Alex"}), (us2:User {name:"Jia Li"}), (us3:User {name:"Ramesh"}), (us4:User {name:"Jennifer"}),
(us5:User {name:"Newton"}), (us6:User {name:"Mozart"}), (us7:User {name:"Picasso"}), (us8:User {name:"Elizabeth"});
CREATE (po1:Post {heading:"Trip to Indy", content:"Had fun at Central Canal"}),
(po2:Post {heading:"Celebrated my Birthday", content:"It was great to have so many friends at my place"}),
(po3:Post {heading:"Tiring Day!", content:"Eagerly waiting for Thanksgiving break!"}),
(po4:Post {heading:"Visiting LA", content:"Looking forward to a great trip!"});
CREATE (po5:Post {heading:"Boiler Up!", content:"Going to the game tomorrow"});
CREATE (po6:Post {heading:"Studying!", content:"Long homework!"});
CREATE (po7:Post {heading:"Sleepy!", content:"had to stay late to submit 348 project on time"});
CREATE (la1:Location {name:"West Lafayette"}), (la2:Location {name:"Chicago"});
CREATE (ca1:Category {name:"Travel"}), (ca2:Category {name:"Celebration"}), (ca3:Category {name:"Festival"}), (ca4:Category {name:"Other"});
// PART 2 : Creating Relations (Edges) // IMPORTANT NOTE on the relations
// 1) Each user can write zero or more posts (Wrote edge)
// 2) Each post can have only one associated location (PostedFrom edge)
// 3) Each post can be categorized under 1 or more categories (CategorizedAs edge)
// 4) Each post can have 0 or more comments from a given user (Commented edge)
// 5) Each post can only have 0 or 1 rating from a given user (Rated edge)
// 6) Each user can be followed by 0 or more users (Follows edge)
Match (us1:User {name:"Alex"}), (us2:User {name:"Jia Li"}), (us3:User {name:"Ramesh"}), (us4:User {name:"Jennifer"}),
(us5:User {name:"Newton"}), (us6:User {name:"Mozart"}), (us7:User {name:"Picasso"}), (us8:User {name:"Elizabeth"}),
(po1:Post {heading:"Trip to Indy", content:"Had fun at Central Canal"}),
(po2:Post {heading:"Celebrated my Birthday", content:"It was great to have so many friends at my place"}),
(po3:Post {heading:"Tiring Day!", content:"Eagerly waiting for Thanksgiving break!"}),
(po4:Post {heading:"Visiting LA", content:"Looking forward to a great trip!"}),
(la1:Location {name:"West Lafayette"}), (la2:Location {name:"Chicago"}),
(ca1:Category {name:"Travel"}), (ca2:Category {name:"Celebration"}), (ca3:Category {name:"Festival"}), (ca4:Category {name:"Other"})
CREATE (us1)-[:Wrote]->(po1),
(us2)-[:Wrote]->(po2),
(us3)-[:Wrote]->(po3),
(us4)-[:Wrote]->(po4),
(po1)-[:PostedFrom]->(la1),
(po2)-[:PostedFrom]->(la1),
(po3)-[:PostedFrom]->(la1),
(po4)-[:PostedFrom]->(la2),
(po1)-[:CategorizedAs]->(ca1),
(po2)-[:CategorizedAs]->(ca2),
(po3)-[:CategorizedAs]->(ca3),
(po4)-[:CategorizedAs]->(ca1),
(po3)-[:CategorizedAs]->(ca4),
(us1)-[:Commented {content: "Did you go to the zoo?"}]->(po1),
(us3)-[:Commented {content: "Thanks for the party!"}]->(po2),
(us4)-[:Commented {content: "See you soon!"}]->(po3),
(us5)-[:Commented {content: "Have fun!"}]->(po4),
(us1)-[:Rated {rating:5}]->(po4),
(us3)-[:Rated {rating:3}]->(po2),
(us4)-[:Rated {rating:1}]->(po1),
(us5)-[:Rated {rating:2}]->(po1),
(us3)-[:Rated {rating:5}]->(po4),
(us7)-[:Rated {rating:5}]->(po4),
(us8)-[:Rated {rating:4}]->(po1),
(us4)-[:Rated {rating:4}]->(po3),
(us4)-[:Rated {rating:1}]->(po2),
(us2)-[:Rated {rating:1}]->(po1),
(us1)-[:Follows]->(us2),
(us2)-[:Follows]->(us3),
(us3)-[:Follows]->(us4),
(us4)-[:Follows]->(us5),
(us5)-[:Follows]->(us6),
(us6)-[:Follows]->(us7),
(us7)-[:Follows]->(us8),
(us2)-[:Follows]->(us8),
(us4)-[:Follows]->(us8);
Match (po5:Post {heading:"Boiler Up!", content:"Going to the game tomorrow"}),
(po6:Post {heading:"Studying!", content:"Long homework!"}),
(po7:Post {heading:"Sleepy!", content:"had to stay late to submit 348 project on time"}),
(us1:User {name:"Alex"})
Create (us1)-[:Rated {rating:5}]->(po5),
(us1)-[:Commented {content: "Keep up the good work"}]->(po6);Queries 1:
Find all the users who wrote a post that was categorized as "Other"
Expected Output :
╒════════╤══════════════╤══════════╕
│"user" │"post_heading"│"category"│
╞════════╪══════════════╪══════════╡
│"Ramesh"│"Tiring Day!" │"Other" │
└────────┴──────────────┴──────────┘Query 2:
Find all the users who wrote a post that was categorized as "Celebration" or "Travel"
Expected Output :
╒══════════╤════════════════════════╤═════════════╕
│"user" │"post_heading" │"category" │
╞══════════╪════════════════════════╪═════════════╡
│"Alex" │"Trip to Indy" │"Travel" │
├──────────┼────────────────────────┼─────────────┤
│"Jia Li" │"Celebrated my Birthday"│"Celebration"│
├──────────┼────────────────────────┼─────────────┤
│"Jennifer"│"Visiting LA" │"Travel" │
└──────────┴────────────────────────┴─────────────┘Query 3:
Find the users who rated or commented on post with heading "Trip to Indy"
Expected Output :
╒═══════════╤════════════════════╤══════════════╕
│"user" │"rated_or_commented"│"post_heading"│
╞═══════════╪════════════════════╪══════════════╡
│"Jia Li" │"Rated" │"Trip to Indy"│
├───────────┼────────────────────┼──────────────┤
│"Elizabeth"│"Rated" │"Trip to Indy"│
├───────────┼────────────────────┼──────────────┤
│"Newton" │"Rated" │"Trip to Indy"│
├───────────┼────────────────────┼──────────────┤
│"Jennifer" │"Rated" │"Trip to Indy"│
├───────────┼────────────────────┼──────────────┤
│"Alex" │"Commented" │"Trip to Indy"│
└───────────┴────────────────────┴──────────────┘Query 4:
Find the users who rated a post of a user that they follow
Expected Output :
╒════════════╤══════════════╤═════════════╕
│"user_rated"│"post_heading"│"user_posted"│
╞════════════╪══════════════╪═════════════╡
│"Ramesh" │"Visiting LA" │"Jennifer" │
└────────────┴──────────────┴─────────────┘Query 5:
Find the posts with average rating greater than 3.5
Expected Output :
╒══════════════╤═════════╕
│"post_heading"│"average"│
╞══════════════╪═════════╡
│"Tiring Day!" │4.0 │
├──────────────┼─────────┤
│"Visiting LA" │5.0 │
├──────────────┼─────────┤
│"Boiler Up!" │5.0 │
└──────────────┴─────────┘Query 6:
List all the category, location pairs and the number of posts that correspond to each such pair
Expected Output :
╒════════════════╤═════════════╤════════════╕
│"location" │"category" │"post_count"│
╞════════════════╪═════════════╪════════════╡
│"West Lafayette"│"Travel" │1 │
├────────────────┼─────────────┼────────────┤
│"West Lafayette"│"Celebration"│1 │
├────────────────┼─────────────┼────────────┤
│"West Lafayette"│"Other" │1 │
├────────────────┼─────────────┼────────────┤
│"West Lafayette"│"Festival" │1 │
├────────────────┼─────────────┼────────────┤
│"Chicago" │"Travel" │1 │
└────────────────┴─────────────┴────────────┘Query 7:
For each category, location pair, find the number of posts that correspond to each such pair. This time only consider posts that have received at least one 5 star rating.
Expected Output :
╒══════════╤══════════╤════════════╕
│"location"│"category"│"post_count"│
╞══════════╪══════════╪════════════╡
│"Chicago" │"Travel" │1 │
└──────────┴──────────┴────────────┘Query 8:
For each post, find the number of users who posted comment(s) on it and the number of users who rated it
Expected Output :
╒════════════════════════╤═════════════╤═════════════════╕
│"post_heading" │"rated_count"│"commented_count"│
╞════════════════════════╪═════════════╪═════════════════╡
│"Trip to Indy" │4 │1 │
├────────────────────────┼─────────────┼─────────────────┤
│"Celebrated my Birthday"│2 │1 │
├────────────────────────┼─────────────┼─────────────────┤
│"Tiring Day!" │1 │1 │
├────────────────────────┼─────────────┼─────────────────┤
│"Visiting LA" │3 │1 │
├────────────────────────┼─────────────┼─────────────────┤
│"Boiler Up!" │1 │0 │
├────────────────────────┼─────────────┼─────────────────┤
│"Studying!" │0 │1 │
├────────────────────────┼─────────────┼─────────────────┤
│"Sleepy!" │0 │0 │
└────────────────────────┴─────────────┴─────────────────┘Query 9:
Count the number of 5-star ratings for posts written by each user. Consider only posts submitted from Chicago (location is Chicago).
Expected Output :
╒══════════╤══════════════════════════════════════════════╕
│"user" │"5-star_ratings_for_posts_written_by_the_user"│
╞══════════╪══════════════════════════════════════════════╡
│"Jennifer"│ 3 │
└──────────┴──────────────────────────────────────────────┘To get solution of above problem you can contact us at:
We are also providing other database related help with an affordable price.
Comments