Qcon 2016 graph

1 - import the qcon schedule.cql

create index on :Company(name);
create index on :Day(date);
create index on :Time(time)
create index on :Room(name);
create index on :Session(title);
create index on :Track(name);


//ALL IN ONE QUERY
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063" as hosts
merge (p:Person {name: hosts.Name, title: hosts.Title})
merge (c:Company {name: hosts.Company})
merge (p)-[:WORKS_FOR]->(c)
with hosts
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (d:Day {date: toInt(csv.day)})
with csv
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2)
with csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c)
with csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d)
with csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title}), (h:Person {name: csv.host})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t)
merge (h)-[:HOSTS]->(t);

2- import qcon schedule v2.cql

create index on :Company(name);
create index on :Day(date);
create index on :Time(time)
create index on :Room(name);
create index on :Session(title);
create index on :Track(name);

schema await;

// HOSTS: https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063

//add the hosts
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063" as csv
merge (p:Person {name: csv.Name, title: csv.Title})
merge (c:Company {name: csv.Company})
merge (p)-[:WORKS_FOR]->(c);


// schedule
https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0

//add the days
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (d:Day {date: toInt(csv.day)});

//connect the days
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2);

//add the rooms, traxcks, speakers and speaker's companies
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c);

//add the timeslots to each day
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d);

//add the sessions and connect them up
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title}), (h:Person {name: csv.host})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t)
merge (h)-[:HOSTS]->(t);

3 - query the qcon schedule.cql

//Querying the Qcon Schedule

//Look at day 1
match (d:Day {date:20160307})<--(t:Time)<--(s:Session)--(connections)
return d,t,s,connections
limit 50

//Look at two people
match (p1:Person), (p2:Person),
path = allshortestpaths( (p1)-[*]-(p2) )
where p1.name contains "Hunger"
and p2.name contains "Webber"
return path

//Look at a person and a company
match (c:Company {name:"ThoughtWorks"}), (p:Person {name:"Jim Webber"}),
path = allshortestpaths( (c)-[*]-(p) )
return path

//Look at sessions with more than one speaker
match (s:Session)-[r:SPEAKS_IN]-(p:Person)
with s, collect(p) as person, count(p) as count
where count > 1
return s,person