Created
August 17, 2019 05:06
-
-
Save maxdemarzi/0fad4db15ad7a6044a5dbb30b779e35e to your computer and use it in GitHub Desktop.
Cypher Scripts for Neo4j Fraud Blog Post
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
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) | |
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
// 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