Created
May 13, 2014 21:22
-
-
Save kbastani/66482fd47d089a13e8bf to your computer and use it in GitHub Desktop.
Who owns the fish?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| = Logical Graphs = | |
| :neo4j-version: 2.0.3 | |
| :author: Kenny Bastani | |
| :twitter: @kennybastani | |
| image::https://c2.staticflickr.com/4/3034/3047586737_9ba0b25fbf.jpg[Who owns the fish?] | |
| There are five houses in five different colours starting from left to right. In each house lives a person of a different nationality. These owners all drink a certain type of beverage, smoke a certain brand of cigarette and keep a certain type of pet. No two owners have the same pet, smoke the same brand or drink the same beverage. The question is: WHO OWNS THE FISH??? | |
| == Problem | |
| * Build a dependency graph in Neo4j that solves the problem of Einstein's famous riddle using Cypher. | |
| == Questions | |
| * Who owns the fish? | |
| == Graph data model | |
| image::http://raw.githubusercontent.com/kbastani/meetup-analytics/master/docs/images/meetup-analytics-graph-gist.png[Meetup Analytics Graph Data Model] | |
| == Create sample dataset | |
| //setup | |
| //hide | |
| [source,cypher] | |
| ---- | |
| // Setup tags, location, groups | |
| MERGE (nosql:Tag { tag: "NoSQL" }) | |
| MERGE (neo4j:Tag { tag: "Neo4j" }) | |
| MERGE (mongodb:Tag { tag: "MongoDB" }) | |
| MERGE (redis:Tag { tag: "Redis" }) | |
| MERGE (cassandra:Tag { tag: "Cassandra" }) | |
| MERGE (couchbase:Tag { tag: "Couchbase" }) | |
| MERGE (riak:Tag { tag: "Riak" }) | |
| MERGE (sanfrancisco:Location { city: "San Francisco" }) | |
| MERGE (graphdbgroup:Group { name: "Graph Database San Francisco" }) | |
| MERGE (redisgroup:Group { name: "The San Francisco Redis Meetup Group" }) | |
| MERGE (mongodbgroup:Group { name: "San Francisco MongoDB User Group" }) | |
| MERGE (cassandragroup:Group { name: "Silicon Valley Apache Cassandra User Group" }) | |
| MERGE (couchbasegroup:Group { name: "The San Francisco Couchbase Group" }) | |
| MERGE (riakgroup:Group { name: "San Francisco Riak Meetup" }) | |
| // Setup tag relationships | |
| MERGE (nosql)<-[:HAS_TAG]-(graphdbgroup) | |
| MERGE (neo4j)<-[:HAS_TAG]-(graphdbgroup) | |
| MERGE (nosql)<-[:HAS_TAG]-(mongodbgroup) | |
| MERGE (mongodb)<-[:HAS_TAG]-(mongodbgroup) | |
| MERGE (nosql)<-[:HAS_TAG]-(redisgroup) | |
| MERGE (redis)<-[:HAS_TAG]-(redisgroup) | |
| MERGE (nosql)<-[:HAS_TAG]-(cassandragroup) | |
| MERGE (cassandra)<-[:HAS_TAG]-(cassandragroup) | |
| MERGE (nosql)<-[:HAS_TAG]-(couchbasegroup) | |
| MERGE (couchbase)<-[:HAS_TAG]-(couchbasegroup) | |
| MERGE (nosql)<-[:HAS_TAG]-(riakgroup) | |
| MERGE (riak)<-[:HAS_TAG]-(riakgroup) | |
| // Setup location relationships | |
| MERGE (graphdbgroup)-[:LOCATED_IN]->(sanfrancisco) | |
| MERGE (redisgroup)-[:LOCATED_IN]->(sanfrancisco) | |
| MERGE (mongodbgroup)-[:LOCATED_IN]->(sanfrancisco) | |
| MERGE (cassandragroup)-[:LOCATED_IN]->(sanfrancisco) | |
| MERGE (couchbasegroup)-[:LOCATED_IN]->(sanfrancisco) | |
| MERGE (riakgroup)-[:LOCATED_IN]->(sanfrancisco) | |
| // Setup days | |
| MERGE (day1:Day { day: 1, month: 1, year: 2014, timestamp: 10000000 }) | |
| MERGE (day2:Day { day: 2, month: 1, year: 2014, timestamp: 10000001 }) | |
| MERGE (day3:Day { day: 3, month: 1, year: 2014, timestamp: 10000002 }) | |
| MERGE (day4:Day { day: 4, month: 1, year: 2014, timestamp: 10000003 }) | |
| MERGE (day5:Day { day: 5, month: 1, year: 2014, timestamp: 10000004 }) | |
| MERGE (day6:Day { day: 6, month: 1, year: 2014, timestamp: 10000005 }) | |
| MERGE (day7:Day { day: 7, month: 1, year: 2014, timestamp: 10000006 }) | |
| // Setup day relationships | |
| MERGE (day1)-[:NEXT]->(day2) | |
| MERGE (day2)-[:NEXT]->(day3) | |
| MERGE (day3)-[:NEXT]->(day4) | |
| MERGE (day4)-[:NEXT]->(day5) | |
| MERGE (day5)-[:NEXT]->(day6) | |
| MERGE (day6)-[:NEXT]->(day7) | |
| // Setup member counts: Graph Database SF | |
| CREATE (graphdbMembers1:Stats { count: 101 }) | |
| CREATE (graphdbMembers2:Stats { count: 105 }) | |
| CREATE (graphdbMembers3:Stats { count: 108 }) | |
| CREATE (graphdbMembers4:Stats { count: 116 }) | |
| CREATE (graphdbMembers5:Stats { count: 123 }) | |
| CREATE (graphdbMembers6:Stats { count: 125 }) | |
| CREATE (graphdbMembers7:Stats { count: 132 }) | |
| // Setup member counts: The San Francisco Redis Meetup Group | |
| CREATE (redisMembers1:Stats { count: 56 }) | |
| CREATE (redisMembers2:Stats { count: 58 }) | |
| CREATE (redisMembers3:Stats { count: 62 }) | |
| CREATE (redisMembers4:Stats { count: 64 }) | |
| CREATE (redisMembers5:Stats { count: 66 }) | |
| CREATE (redisMembers6:Stats { count: 68 }) | |
| CREATE (redisMembers7:Stats { count: 70 }) | |
| // Setup member counts: San Francisco MongoDB User Group | |
| CREATE (mongodbMembers1:Stats { count: 120 }) | |
| CREATE (mongodbMembers2:Stats { count: 122 }) | |
| CREATE (mongodbMembers3:Stats { count: 124 }) | |
| CREATE (mongodbMembers4:Stats { count: 126 }) | |
| CREATE (mongodbMembers5:Stats { count: 128 }) | |
| CREATE (mongodbMembers6:Stats { count: 130 }) | |
| CREATE (mongodbMembers7:Stats { count: 132 }) | |
| // Setup member counts: Silicon Valley Apache Cassandra User Group | |
| CREATE (cassandraMembers1:Stats { count: 91 }) | |
| CREATE (cassandraMembers2:Stats { count: 98 }) | |
| CREATE (cassandraMembers3:Stats { count: 105 }) | |
| CREATE (cassandraMembers4:Stats { count: 109 }) | |
| CREATE (cassandraMembers5:Stats { count: 112 }) | |
| CREATE (cassandraMembers6:Stats { count: 115 }) | |
| CREATE (cassandraMembers7:Stats { count: 122 }) | |
| // Setup member counts: The San Francisco Couchbase Group | |
| CREATE (couchbaseMembers1:Stats { count: 44 }) | |
| CREATE (couchbaseMembers2:Stats { count: 46 }) | |
| CREATE (couchbaseMembers3:Stats { count: 48 }) | |
| CREATE (couchbaseMembers4:Stats { count: 52 }) | |
| CREATE (couchbaseMembers5:Stats { count: 55 }) | |
| CREATE (couchbaseMembers6:Stats { count: 59 }) | |
| CREATE (couchbaseMembers7:Stats { count: 64 }) | |
| // Setup member counts: San Francisco Riak Meetup | |
| CREATE (riakMembers1:Stats { count: 34 }) | |
| CREATE (riakMembers2:Stats { count: 36 }) | |
| CREATE (riakMembers3:Stats { count: 39 }) | |
| CREATE (riakMembers4:Stats { count: 43 }) | |
| CREATE (riakMembers5:Stats { count: 47 }) | |
| CREATE (riakMembers6:Stats { count: 49 }) | |
| CREATE (riakMembers7:Stats { count: 52 }) | |
| // Setup member relationships: Graph Database SF | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers1)-[:ON_DAY]->(day1) | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers2)-[:ON_DAY]->(day2) | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers3)-[:ON_DAY]->(day3) | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers4)-[:ON_DAY]->(day4) | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers5)-[:ON_DAY]->(day5) | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers6)-[:ON_DAY]->(day6) | |
| MERGE (graphdbgroup)-[:HAS_MEMBERS]->(graphdbMembers7)-[:ON_DAY]->(day7) | |
| // Setup member relationships: The San Francisco Redis Meetup Group | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers1)-[:ON_DAY]->(day1) | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers2)-[:ON_DAY]->(day2) | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers3)-[:ON_DAY]->(day3) | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers4)-[:ON_DAY]->(day4) | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers5)-[:ON_DAY]->(day5) | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers6)-[:ON_DAY]->(day6) | |
| MERGE (redisgroup)-[:HAS_MEMBERS]->(redisMembers7)-[:ON_DAY]->(day7) | |
| // Setup member relationships: San Francisco MongoDB User Group | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers1)-[:ON_DAY]->(day1) | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers2)-[:ON_DAY]->(day2) | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers3)-[:ON_DAY]->(day3) | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers4)-[:ON_DAY]->(day4) | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers5)-[:ON_DAY]->(day5) | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers6)-[:ON_DAY]->(day6) | |
| MERGE (mongodbgroup)-[:HAS_MEMBERS]->(mongodbMembers7)-[:ON_DAY]->(day7) | |
| // Setup member relationships: Silicon Valley Apache Cassandra User Group | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers1)-[:ON_DAY]->(day1) | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers2)-[:ON_DAY]->(day2) | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers3)-[:ON_DAY]->(day3) | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers4)-[:ON_DAY]->(day4) | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers5)-[:ON_DAY]->(day5) | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers6)-[:ON_DAY]->(day6) | |
| MERGE (cassandragroup)-[:HAS_MEMBERS]->(cassandraMembers7)-[:ON_DAY]->(day7) | |
| // Setup member relationships: The San Francisco Couchbase Group | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers1)-[:ON_DAY]->(day1) | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers2)-[:ON_DAY]->(day2) | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers3)-[:ON_DAY]->(day3) | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers4)-[:ON_DAY]->(day4) | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers5)-[:ON_DAY]->(day5) | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers6)-[:ON_DAY]->(day6) | |
| MERGE (couchbasegroup)-[:HAS_MEMBERS]->(couchbaseMembers7)-[:ON_DAY]->(day7) | |
| // Setup member relationships: San Francisco Riak Meetup | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers1)-[:ON_DAY]->(day1) | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers2)-[:ON_DAY]->(day2) | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers3)-[:ON_DAY]->(day3) | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers4)-[:ON_DAY]->(day4) | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers5)-[:ON_DAY]->(day5) | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers6)-[:ON_DAY]->(day6) | |
| MERGE (riakgroup)-[:HAS_MEMBERS]->(riakMembers7)-[:ON_DAY]->(day7) | |
| ---- | |
| //graph | |
| == Reporting | |
| The following queries answer the questions required for our meetup analytics platform. | |
| === What is the daily membership count for the meetup group Graph Databases - San Francisco? | |
| Since GraphGists are only meant to test a small dataset, I've chosen to use 7 days of data for my sample. These days range from `1/1/2014` to `1/7/2014`. These are the start and end dates I will use for this query. Let's find the time series that plots membership growth for the meetup group: `Graph Database San Francisco`. | |
| [source,cypher] | |
| ---- | |
| MATCH (dayStart:Day { day: 1, month: 1, year: 2014 }), | |
| (dayEnd:Day { day: 7, month: 1, year: 2014 }) | |
| MATCH (dayStart)-[:NEXT*0..]->(day:Day)-[:NEXT*0..]->(dayEnd) | |
| MATCH (day)<-[:ON_DAY]-(members:Stats), | |
| (members)<-[:HAS_MEMBERS]-(group:Group { name: "Graph Database San Francisco" }) | |
| RETURN (day.month + "/" + day.day + "/" + day.year) AS date, group.name as group_name, members.count AS membership_count | |
| ORDER BY day.timestamp | |
| ---- | |
| //table | |
| === What is the daily membership count for NoSQL meetup groups in San Francisco? | |
| Using the same start and end dates from the last query, let's find the time series that plots the combined membership growth for the tag: `NoSQL`. | |
| [source,cypher] | |
| ---- | |
| MATCH (dayStart:Day { day: 1, month: 1, year: 2014 }), | |
| (dayEnd:Day { day: 7, month: 1, year: 2014 }) | |
| MATCH (dayStart)-[:NEXT*0..]->(day:Day)-[:NEXT*0..]->(dayEnd) | |
| MATCH (day)<-[:ON_DAY]-(members:Stats), | |
| (members)<-[:HAS_MEMBERS]-(group:Group), | |
| (group)-[:HAS_TAG]->(tag:Tag { tag: "NoSQL" }), | |
| (group)-[:LOCATED_IN]->(city { city: "San Francisco" }) | |
| WITH day, tag, members | |
| ORDER by day.timestamp | |
| WITH (day.month + "/" + day.day + "/" + day.year) AS date, tag.tag as topic, sum(members.count) AS membership_count, day.timestamp as timestamp | |
| RETURN date, topic, membership_count | |
| ORDER BY timestamp | |
| ---- | |
| //table | |
| === From the previous query, generate a time series for a basic line chart | |
| Using the popular Javascript charting plugin `Highcharts.js`, we can return back valid JSON objects as properties from our query to use for the JSON parameters of the `Highchart.js` setup options. For this time series we want to use the basic line chart, which is documented here: http://www.highcharts.com/demo/[http://www.highcharts.com/demo/] | |
| The two options we need to generate property data for are `categories`, which contain the ordered date labels on the X-Axis, and `series`, which contains the ordered membership counts for each meetup group. The result of that query is below, which I've used to generate a line chart of our time series. | |
| [source,cypher] | |
| ---- | |
| MATCH (dayStart:Day { day: 1, month: 1, year: 2014 }), | |
| (dayEnd:Day { day: 7, month: 1, year: 2014 }) | |
| MATCH (dayStart)-[:NEXT*0..]->(day:Day)-[:NEXT*0..]->(dayEnd) | |
| MATCH (day)<-[:ON_DAY]-(members:Stats), | |
| (members)<-[:HAS_MEMBERS]-(group:Group), | |
| (group)-[:HAS_TAG]->(tag:Tag { tag: "NoSQL" }), | |
| (group)-[:LOCATED_IN]->(city { city: "San Francisco" }) | |
| WITH (day.month + "/" + day.day + "/" + day.year) AS date, group.name AS name, members.count AS membership_count | |
| WITH collect("'" + date + "'") as categories, name, collect(membership_count) as series | |
| RETURN categories, collect({ name:name,data: series }) AS series | |
| ---- | |
| //table |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
the created triples are not related to Einstein's riddle.