Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Cypher Scripts for Neo4j Fraud Blog Post
CREATE (john:User {name:"John"})
CREATE (sheila:User {name:"Sheila"})
CREATE (robert:User {name:"Robert"})
CREATE (karen:User {name:"Karen"})
CREATE (m1:Merchant {name:"Computer Store"})
CREATE (m2:Merchant {name:"Gas Station"})
CREATE (m3:Merchant {name:"Jewelry Store"})
CREATE (m4:Merchant {name:"Furniture Store"})
CREATE (m5:Merchant {name:"Optometrist"})
CREATE (m6:Merchant {name:"Coffee Shop"})
CREATE (m7:Merchant {name:"Corner Store"})
CREATE (m8:Merchant {name:"Online Gift Card Merchant"})
CREATE (m9:Merchant {name:"Online Audio Book Merchant"})
CREATE (m10:Merchant {name:"Gas Station #2"})
CREATE (m11:Merchant {name:"Electronics Store"})
CREATE (m12:Merchant {name:"Vehicle Maintenance Merchant"})
CREATE (m13:Merchant {name:"French Restaurant"})
CREATE (m14:Merchant {name:"Mexican Restaurant"})
CREATE (m15:Merchant {name:"Chinese Restaurant"})
CREATE (m16:Merchant {name:"Indian Restaurant"})
CREATE (m17:Merchant {name:"Japanese Restaurant"})
CREATE (m18:Merchant {name:"Sports Bar"})
CREATE (m19:Merchant {name:"Pool Hall"})
CREATE (m20:Merchant {name:"Bowling Alley"})
CREATE (tx1:Transaction:Fraudulent {amount: 2000.00, date:datetime()})
CREATE (tx2:Transaction {amount: 35.00, date:datetime() - duration('P1D')})
CREATE (tx3:Transaction {amount: 25.00, date:datetime() - duration('P2D')})
CREATE (tx4:Transaction {amount: 12.00, date:datetime() - duration('P3D')})
CREATE (tx5:Transaction {amount: 87.00, date:datetime() - duration('P4D')})
CREATE (tx6:Transaction {amount: 10.00, date:datetime() - duration('P5D')})
CREATE (tx7:Transaction {amount: 30.00, date:datetime() - duration('P6D')})
CREATE (tx8:Transaction {amount: 25.00, date:datetime() - duration('P7D')})
CREATE (tx9:Transaction {amount: 62.00, date:datetime() - duration('P8D')})
CREATE (tx10:Transaction {amount: 4.00, date:datetime() - duration('P9D')})
CREATE (tx11:Transaction {amount: 90.00, date:datetime() - duration('P10D')})
CREATE (tx12:Transaction {amount: 73.00, date:datetime() - duration('P11D')})
CREATE (tx13:Transaction {amount: 26.00, date:datetime() - duration('P12D')})
CREATE (tx14:Transaction {amount: 108.00, date:datetime() - duration('P13D')})
CREATE (tx15:Transaction {amount: 50.00, date:datetime() - duration('P14D')})
CREATE (tx16:Transaction {amount: 40.00, date:datetime()})
CREATE (tx17:Transaction {amount: 38.00, date:datetime() - duration('P1D')})
CREATE (tx18:Transaction {amount: 49.00, date:datetime() - duration('P2D')})
CREATE (tx19:Transaction:Fraudulent {amount: 3000.00, date:datetime() - duration('P3D')})
CREATE (tx20:Transaction {amount: 66.00, date:datetime() - duration('P4D')})
CREATE (tx21:Transaction {amount: 12.00, date:datetime() - duration('P5D')})
CREATE (tx22:Transaction {amount: 3.00, date:datetime() - duration('P6D')})
CREATE (tx23:Transaction {amount: 40.00, date:datetime() - duration('P7D')})
CREATE (tx24:Transaction {amount: 8.00, date:datetime() - duration('P8D')})
CREATE (tx25:Transaction {amount: 2.00, date:datetime() - duration('P9D')})
CREATE (tx26:Transaction {amount: 76.00, date:datetime() - duration('P10D')})
CREATE (tx27:Transaction {amount: 33.00, date:datetime() - duration('P11D')})
CREATE (tx28:Transaction {amount: 24.00, date:datetime() - duration('P12D')})
CREATE (tx29:Transaction {amount: 17.00, date:datetime() - duration('P13D')})
CREATE (tx30:Transaction {amount: 22.00, date:datetime() - duration('P14D')})
CREATE (tx31:Transaction {amount: 37.00, date:datetime()})
CREATE (tx32:Transaction {amount: 78.00, date:datetime() - duration('P1D')})
CREATE (tx33:Transaction {amount: 23.00, date:datetime() - duration('P2D')})
CREATE (tx34:Transaction {amount: 10.00, date:datetime() - duration('P3D')})
CREATE (tx35:Transaction {amount: 19.00, date:datetime() - duration('P4D')})
CREATE (tx36:Transaction {amount: 37.00, date:datetime() - duration('P5D')})
CREATE (tx37:Transaction {amount: 44.00, date:datetime() - duration('P6D')})
CREATE (tx38:Transaction {amount: 18.00, date:datetime() - duration('P7D')})
CREATE (tx39:Transaction {amount: 62.00, date:datetime() - duration('P8D')})
CREATE (tx40:Transaction {amount: 32.00, date:datetime() - duration('P9D')})
CREATE (tx41:Transaction {amount: 76.00, date:datetime() - duration('P10D')})
CREATE (tx42:Transaction {amount: 6.00, date:datetime() - duration('P11D')})
CREATE (tx43:Transaction {amount: 31.00, date:datetime() - duration('P12D')})
CREATE (tx44:Transaction {amount: 36.00, date:datetime() - duration('P13D')})
CREATE (tx45:Transaction {amount: 65.00, date:datetime() - duration('P14D')})
CREATE (tx46:Transaction {amount: 40.00, date:datetime()})
CREATE (tx47:Transaction {amount: 23.00, date:datetime() - duration('P1D')})
CREATE (tx48:Transaction:Fraudulent {amount: 1500.00, date:datetime() - duration('P2D')})
CREATE (tx49:Transaction {amount: 35.00, date:datetime() - duration('P3D')})
CREATE (tx50:Transaction {amount: 12.00, date:datetime() - duration('P4D')})
CREATE (tx51:Transaction {amount: 25.00, date:datetime() - duration('P5D')})
CREATE (tx52:Transaction {amount: 40.00, date:datetime() - duration('P6D')})
CREATE (tx53:Transaction {amount: 8.00, date:datetime() - duration('P7D')})
CREATE (tx54:Transaction {amount: 12.00, date:datetime() - duration('P8D')})
CREATE (tx55:Transaction {amount: 3.00, date:datetime() - duration('P9D')})
CREATE (tx56:Transaction {amount: 36.00, date:datetime() - duration('P10D')})
CREATE (tx57:Transaction {amount: 15.00, date:datetime() - duration('P11D')})
CREATE (tx58:Transaction {amount: 19.00, date:datetime() - duration('P12D')})
CREATE (tx59:Transaction {amount: 27.00, date:datetime() - duration('P13D')})
CREATE (tx60:Transaction {amount: 42.00, date:datetime() - duration('P14D')})
CREATE (tx1)-[:AT_MERCHANT]->(m1)
CREATE (tx2)-[:AT_MERCHANT]->(m8)
CREATE (tx3)-[:AT_MERCHANT]->(m12)
CREATE (tx4)-[:AT_MERCHANT]->(m7)
CREATE (tx5)-[:AT_MERCHANT]->(m20)
CREATE (tx6)-[:AT_MERCHANT]->(m16)
CREATE (tx7)-[:AT_MERCHANT]->(m11)
CREATE (tx8)-[:AT_MERCHANT]->(m9)
CREATE (tx9)-[:AT_MERCHANT]->(m10)
CREATE (tx10)-[:AT_MERCHANT]->(m2)
CREATE (tx11)-[:AT_MERCHANT]->(m9)
CREATE (tx12)-[:AT_MERCHANT]->(m17)
CREATE (tx13)-[:AT_MERCHANT]->(m12)
CREATE (tx14)-[:AT_MERCHANT]->(m7)
CREATE (tx15)-[:AT_MERCHANT]->(m8)
CREATE (tx16)-[:AT_MERCHANT]->(m20)
CREATE (tx17)-[:AT_MERCHANT]->(m13)
CREATE (tx18)-[:AT_MERCHANT]->(m10)
CREATE (tx19)-[:AT_MERCHANT]->(m3)
CREATE (tx20)-[:AT_MERCHANT]->(m10)
CREATE (tx21)-[:AT_MERCHANT]->(m2)
CREATE (tx22)-[:AT_MERCHANT]->(m5)
CREATE (tx23)-[:AT_MERCHANT]->(m9)
CREATE (tx24)-[:AT_MERCHANT]->(m14)
CREATE (tx25)-[:AT_MERCHANT]->(m2)
CREATE (tx26)-[:AT_MERCHANT]->(m16)
CREATE (tx27)-[:AT_MERCHANT]->(m20)
CREATE (tx28)-[:AT_MERCHANT]->(m7)
CREATE (tx29)-[:AT_MERCHANT]->(m6)
CREATE (tx30)-[:AT_MERCHANT]->(m9)
CREATE (tx31)-[:AT_MERCHANT]->(m8)
CREATE (tx32)-[:AT_MERCHANT]->(m17)
CREATE (tx33)-[:AT_MERCHANT]->(m13)
CREATE (tx34)-[:AT_MERCHANT]->(m14)
CREATE (tx35)-[:AT_MERCHANT]->(m18)
CREATE (tx36)-[:AT_MERCHANT]->(m19)
CREATE (tx37)-[:AT_MERCHANT]->(m20)
CREATE (tx38)-[:AT_MERCHANT]->(m11)
CREATE (tx39)-[:AT_MERCHANT]->(m12)
CREATE (tx40)-[:AT_MERCHANT]->(m7)
CREATE (tx41)-[:AT_MERCHANT]->(m13)
CREATE (tx42)-[:AT_MERCHANT]->(m2)
CREATE (tx43)-[:AT_MERCHANT]->(m15)
CREATE (tx44)-[:AT_MERCHANT]->(m14)
CREATE (tx45)-[:AT_MERCHANT]->(m1)
CREATE (tx46)-[:AT_MERCHANT]->(m5)
CREATE (tx47)-[:AT_MERCHANT]->(m8)
CREATE (tx48)-[:AT_MERCHANT]->(m4)
CREATE (tx49)-[:AT_MERCHANT]->(m18)
CREATE (tx50)-[:AT_MERCHANT]->(m1)
CREATE (tx51)-[:AT_MERCHANT]->(m17)
CREATE (tx52)-[:AT_MERCHANT]->(m20)
CREATE (tx53)-[:AT_MERCHANT]->(m18)
CREATE (tx54)-[:AT_MERCHANT]->(m5)
CREATE (tx55)-[:AT_MERCHANT]->(m2)
CREATE (tx56)-[:AT_MERCHANT]->(m8)
CREATE (tx57)-[:AT_MERCHANT]->(m14)
CREATE (tx58)-[:AT_MERCHANT]->(m12)
CREATE (tx59)-[:AT_MERCHANT]->(m6)
CREATE (tx60)-[:AT_MERCHANT]->(m8)
CREATE (john)-[:MAKES]->(tx1)
CREATE (john)-[:MAKES]->(tx2)
CREATE (john)-[:MAKES]->(tx3)
CREATE (john)-[:MAKES]->(tx4)
CREATE (john)-[:MAKES]->(tx5)
CREATE (john)-[:MAKES]->(tx6)
CREATE (john)-[:MAKES]->(tx7)
CREATE (john)-[:MAKES]->(tx8)
CREATE (john)-[:MAKES]->(tx9)
CREATE (john)-[:MAKES]->(tx10)
CREATE (john)-[:MAKES]->(tx11)
CREATE (john)-[:MAKES]->(tx12)
CREATE (john)-[:MAKES]->(tx13)
CREATE (john)-[:MAKES]->(tx14)
CREATE (john)-[:MAKES]->(tx15)
CREATE (sheila)-[:MAKES]->(tx16)
CREATE (sheila)-[:MAKES]->(tx17)
CREATE (sheila)-[:MAKES]->(tx18)
CREATE (sheila)-[:MAKES]->(tx19)
CREATE (sheila)-[:MAKES]->(tx20)
CREATE (sheila)-[:MAKES]->(tx21)
CREATE (sheila)-[:MAKES]->(tx22)
CREATE (sheila)-[:MAKES]->(tx23)
CREATE (sheila)-[:MAKES]->(tx24)
CREATE (sheila)-[:MAKES]->(tx25)
CREATE (sheila)-[:MAKES]->(tx26)
CREATE (sheila)-[:MAKES]->(tx27)
CREATE (sheila)-[:MAKES]->(tx28)
CREATE (sheila)-[:MAKES]->(tx29)
CREATE (sheila)-[:MAKES]->(tx30)
CREATE (robert)-[:MAKES]->(tx31)
CREATE (robert)-[:MAKES]->(tx32)
CREATE (robert)-[:MAKES]->(tx33)
CREATE (robert)-[:MAKES]->(tx34)
CREATE (robert)-[:MAKES]->(tx35)
CREATE (robert)-[:MAKES]->(tx36)
CREATE (robert)-[:MAKES]->(tx37)
CREATE (robert)-[:MAKES]->(tx38)
CREATE (robert)-[:MAKES]->(tx39)
CREATE (robert)-[:MAKES]->(tx40)
CREATE (robert)-[:MAKES]->(tx41)
CREATE (robert)-[:MAKES]->(tx42)
CREATE (robert)-[:MAKES]->(tx43)
CREATE (robert)-[:MAKES]->(tx44)
CREATE (robert)-[:MAKES]->(tx45)
CREATE (karen)-[:MAKES]->(tx46)
CREATE (karen)-[:MAKES]->(tx47)
CREATE (karen)-[:MAKES]->(tx48)
CREATE (karen)-[:MAKES]->(tx49)
CREATE (karen)-[:MAKES]->(tx50)
CREATE (karen)-[:MAKES]->(tx51)
CREATE (karen)-[:MAKES]->(tx52)
CREATE (karen)-[:MAKES]->(tx53)
CREATE (karen)-[:MAKES]->(tx54)
CREATE (karen)-[:MAKES]->(tx55)
CREATE (karen)-[:MAKES]->(tx56)
CREATE (karen)-[:MAKES]->(tx57)
CREATE (karen)-[:MAKES]->(tx58)
CREATE (karen)-[:MAKES]->(tx59)
CREATE (karen)-[:MAKES]->(tx60)
CREATE (john)-[:PREV_TX]->(tx1)
CREATE (tx1)-[:PREV_TX]->(tx2)
CREATE (tx2)-[:PREV_TX]->(tx3)
CREATE (tx3)-[:PREV_TX]->(tx4)
CREATE (tx4)-[:PREV_TX]->(tx5)
CREATE (tx5)-[:PREV_TX]->(tx6)
CREATE (tx6)-[:PREV_TX]->(tx7)
CREATE (tx7)-[:PREV_TX]->(tx8)
CREATE (tx8)-[:PREV_TX]->(tx9)
CREATE (tx9)-[:PREV_TX]->(tx10)
CREATE (tx10)-[:PREV_TX]->(tx11)
CREATE (tx11)-[:PREV_TX]->(tx12)
CREATE (tx12)-[:PREV_TX]->(tx13)
CREATE (tx13)-[:PREV_TX]->(tx14)
CREATE (tx14)-[:PREV_TX]->(tx15)
CREATE (sheila)-[:PREV_TX]->(tx16)
CREATE (tx16)-[:PREV_TX]->(tx17)
CREATE (tx17)-[:PREV_TX]->(tx18)
CREATE (tx18)-[:PREV_TX]->(tx19)
CREATE (tx19)-[:PREV_TX]->(tx20)
CREATE (tx20)-[:PREV_TX]->(tx21)
CREATE (tx21)-[:PREV_TX]->(tx22)
CREATE (tx22)-[:PREV_TX]->(tx23)
CREATE (tx23)-[:PREV_TX]->(tx24)
CREATE (tx24)-[:PREV_TX]->(tx25)
CREATE (tx25)-[:PREV_TX]->(tx26)
CREATE (tx26)-[:PREV_TX]->(tx27)
CREATE (tx27)-[:PREV_TX]->(tx28)
CREATE (tx28)-[:PREV_TX]->(tx29)
CREATE (tx29)-[:PREV_TX]->(tx30)
CREATE (robert)-[:PREV_TX]->(tx31)
CREATE (tx31)-[:PREV_TX]->(tx32)
CREATE (tx32)-[:PREV_TX]->(tx33)
CREATE (tx33)-[:PREV_TX]->(tx34)
CREATE (tx34)-[:PREV_TX]->(tx35)
CREATE (tx35)-[:PREV_TX]->(tx36)
CREATE (tx36)-[:PREV_TX]->(tx37)
CREATE (tx37)-[:PREV_TX]->(tx38)
CREATE (tx38)-[:PREV_TX]->(tx39)
CREATE (tx39)-[:PREV_TX]->(tx40)
CREATE (tx40)-[:PREV_TX]->(tx41)
CREATE (tx41)-[:PREV_TX]->(tx42)
CREATE (tx42)-[:PREV_TX]->(tx43)
CREATE (tx43)-[:PREV_TX]->(tx44)
CREATE (tx44)-[:PREV_TX]->(tx45)
CREATE (karen)-[:PREV_TX]->(tx46)
CREATE (tx46)-[:PREV_TX]->(tx47)
CREATE (tx47)-[:PREV_TX]->(tx48)
CREATE (tx48)-[:PREV_TX]->(tx49)
CREATE (tx49)-[:PREV_TX]->(tx50)
CREATE (tx50)-[:PREV_TX]->(tx51)
CREATE (tx51)-[:PREV_TX]->(tx52)
CREATE (tx52)-[:PREV_TX]->(tx53)
CREATE (tx53)-[:PREV_TX]->(tx54)
CREATE (tx54)-[:PREV_TX]->(tx55)
CREATE (tx55)-[:PREV_TX]->(tx56)
CREATE (tx56)-[:PREV_TX]->(tx57)
CREATE (tx57)-[:PREV_TX]->(tx58)
CREATE (tx58)-[:PREV_TX]->(tx59)
CREATE (tx59)-[:PREV_TX]->(tx60)
// The last week of John's transactions using the MAKES relationships
MATCH p = (n:User {name:"John"})-[:MAKES]->(tx)
WHERE tx.date > datetime() - duration('P7D')
RETURN p;
// The last week of John's transactions using the PREV_TX relationships
MATCH p = (n:User {name:"John"})-[:PREV_TX*]->(tx)
WHERE NONE (tx IN nodes(p) WHERE COALESCE(tx.date, datetime()) <= datetime() - duration('P7D'))
RETURN p;
// All the transactions marked fraudulent in the last week and the transactions that came before them up to two weeks ago.
MATCH p = (fraud:Fraudulent)-[:PREV_TX*]->(tx)
WHERE fraud.date > datetime() - duration('P7D')
AND NONE (tx IN nodes(p) WHERE COALESCE(tx.date, datetime()) <= datetime() - duration('P14D'))
RETURN p;
// Top 5 common merchants from fraudulent transaction chains up to two weeks ago.
MATCH p = (fraud:Fraudulent)-[:PREV_TX*]->(tx)
WHERE fraud.date > datetime() - duration('P7D')
AND NONE (tx IN nodes(p) WHERE COALESCE(tx.date, datetime()) <= datetime() - duration('P14D'))
WITH nodes(p) AS transactions
UNWIND transactions AS tx
WITH DISTINCT tx
MATCH (tx)-[:AT_MERCHANT]->(merchant)
RETURN merchant.name, COUNT(*) AS txCount
ORDER BY txCount DESC
LIMIT 5;
MATCH (fraud:Fraudulent)<-[:MAKES]-(user)
WHERE fraud.date > datetime() - duration('P7D')
WITH COLLECT(user) AS users
// Find the transactions that happened before the reported fraud in the last 2 weeks
MATCH p = (fraud:Fraudulent)-[:PREV_TX*]->(tx)
WHERE fraud.date > datetime() - duration('P7D')
AND NONE (tx IN nodes(p) WHERE tx.date <= datetime() - duration('P14D'))
WITH users, nodes(p) AS transactions
// Find out at which merchant these transactions took place
UNWIND transactions AS tx
WITH DISTINCT users, tx
MATCH (user)-[:MAKES]->(tx)-[:AT_MERCHANT]->(merchant)
// Use the number of distinct users who made transactions as a score
WITH users, merchant, COUNT(DISTINCT user) AS userCount
ORDER BY userCount DESC
// Find out who else may be at risk
MATCH (merchant)<-[:AT_MERCHANT]-(tx)<-[:MAKES]-(user)
WHERE tx.date > datetime() - duration('P14D')
AND NOT user IN users
// Which merchants may have been the first sign of fraud
WITH DISTINCT user, COLLECT({merchant: merchant.name, userCount: userCount}) AS potentials, AVG(userCount) AS averageuserCount
WITH user, averageuserCount, potentials
UNWIND potentials AS potential
// Return those with higher than average
WITH user, potential, averageuserCount
WHERE potential.userCount > averageuserCount
RETURN user, potential.merchant, potential.userCount
ORDER BY user, potential.userCount DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.