Skip to content

Instantly share code, notes, and snippets.

@kbastani

kbastani/CasinoStaff-24-7-Alice.CQL

Last active January 3, 2016 22:39
Show Gist options
  • Save kbastani/8529380 to your computer and use it in GitHub Desktop.
Save kbastani/8529380 to your computer and use it in GitHub Desktop.
Download ZIP
Casino staff 24/7
Raw
CasinoStaff-24-7-Alice.CQL
// Setup data for casino employee: Alice
WITH { day: 18, month: 1, year: 2014 } as dayMap
// The dayMap field acts as a parameter for this script
// Merge Alice and his skills
MERGE (baccarat:Skill { name: "Baccarat" })
MERGE (blackjack:Skill { name: "Blackjack" })
MERGE (roulette:Skill { name: "Roulette" })
MERGE (alice:Employee { firstname: "Alice", lastname: "Smith" })
CREATE (alice)-[:HAS_SKILL]->(baccarat)
CREATE (alice)-[:HAS_SKILL]->(blackjack)
CREATE (alice)-[:HAS_SKILL]->(roulette)
// 9:00 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389949201 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 9 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 9:20 AM
MERGE (game:Game { type: "break", interval: 20, unit: "minute", timestamp: 1389950401 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 9 })
CREATE (game)<-[:BREAK]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(baccarat)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 9:40 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389951601 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 9 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 10:00 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389952801 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 10 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(baccarat)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 10:20 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389954001 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 10 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 10:40 AM
MERGE (game:Game { type: "break", interval: 20, unit: "minute", timestamp: 1389955201 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 10 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:BREAK]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(baccarat)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 11:00 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389956401 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 11 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 11:20 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389957601 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 11 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(baccarat)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, alice, baccarat, blackjack
// 11:40 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389958801 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 11 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(alice)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
Raw
CasinoStaff-24-7-Bob.CQL
// Setup data for casino employee: Bob
WITH { day: 18, month: 1, year: 2014 } as dayMap
// The dayMap field acts as a parameter for this script
// Merge Bob and his skills
MERGE (baccarat:Skill { name: "Baccarat" })
MERGE (blackjack:Skill { name: "Blackjack" })
MERGE (bob:Employee { firstname: "Bob", lastname: "Appleseed" })
CREATE (bob)-[:HAS_SKILL]->(baccarat)
CREATE (bob)-[:HAS_SKILL]->(blackjack)
// 9:00 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389949200 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 9 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 9:20 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389950400 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 9 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(baccarat)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 9:40 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389951600 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 9 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 10:00 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389952800 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 10 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(baccarat)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 10:20 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389954000 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 10 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 10:40 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389955200 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 10 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(baccarat)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 11:00 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389956400 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 11 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 11:20 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389957600 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 11 })
MERGE (table:Table { tableid: 67890 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(baccarat)
CREATE (game)-[:LOCATION]->(table)
WITH dayMap, game as lastgame, bob, baccarat, blackjack
// 11:40 AM
MERGE (game:Game { type: "dealer", interval: 20, unit: "minute", timestamp: 1389958800 })
MERGE (hour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 11 })
MERGE (table:Table { tableid: 12345 })
CREATE (game)<-[:CONTINUE]-(lastgame)
CREATE (game)-[:BEGINS]->(hour)
CREATE (game)<-[:DEALS]-(bob)
CREATE (game)-[:IS_SKILL]->(blackjack)
CREATE (game)-[:LOCATION]->(table)
Raw
Question-1.cql
// What staff have been on the floor for 80 minutes or more on a specific day?
WITH { day: 18, month: 1, year: 2014 } as dayMap
// The dayMap field acts as a parameter for this script
MATCH (day:Day { day: dayMap.day, month: dayMap.month, year: dayMap.year }),
(day)-[:FIRST|NEXT*]->(hours:Hour),
(hours)<-[:BEGINS]-(game:Game),
(game)<-[:DEALS]-(employee:Employee)
WITH game, employee
ORDER BY game.timestamp DESC
WITH employee, head(collect(game)) as game
MATCH (game)<-[:CONTINUE*]-(games)
WITH employee.firstname as first_name, employee.lastname as last_name, SUM(games.interval) as time_on_floor
// Only return results for staff on the floor more than 80 minutes
WHERE time_on_floor > 80
RETURN first_name, last_name, time_on_floor
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment