Objective
Machine-learning fraud detection scores one transaction at a time. That catches the lone fraudster whose payments look strange — huge, at 3am, from abroad. It does not catch a fraud ring: five accounts that each spend like an ordinary customer, but share the same phone and the same stolen card numbers.
This recipe builds both views on the same data, in the same engine:
- An anomaly model (isolation forest, no labels) scores all 123 transactions with
ANOMALY_SCORE()inside a plainSELECT. It finds the lone fraudster — and rates every ring member as normal. - A graph of accounts, devices and cards. Starting from the one account with a confirmed chargeback, a single traversal finds the four accounts linked to it through shared identifiers, two to four hops away.
- One review queue that merges both signals.
Step 1: Transactions — 40 ordinary accounts and one lone fraudster
Every account makes three ordinary payments ($20–$199, daytime, domestic). ACC-33 also makes
three large 3am cross-border payments. The ring accounts (ACC-11 to ACC-15) are hidden
among the ordinary ones: nothing in this table marks them.
DROP TABLE IF EXISTS recipe_ring_tx;
CREATE TABLE recipe_ring_tx (
tx_id INT PRIMARY KEY,
account_id TEXT,
amount DOUBLE,
hour_of_day INT,
cross_border INT
);
INSERT INTO recipe_ring_tx (tx_id, account_id, amount, hour_of_day, cross_border) VALUES
(1,'ACC-01',57,14,0),
(2,'ACC-01',110,17,0),
(3,'ACC-01',163,9,0),
(4,'ACC-02',94,19,0),
(5,'ACC-02',147,11,0),
(6,'ACC-02',20,14,0),
(7,'ACC-03',131,13,0),
(8,'ACC-03',184,16,0),
(9,'ACC-03',57,19,0),
(10,'ACC-04',168,18,0),
(11,'ACC-04',41,10,0),
(12,'ACC-04',94,13,0),
(13,'ACC-05',25,12,0),
(14,'ACC-05',78,15,0),
(15,'ACC-05',131,18,0),
(16,'ACC-06',62,17,0),
(17,'ACC-06',115,9,0),
(18,'ACC-06',168,12,0),
(19,'ACC-07',99,11,0),
(20,'ACC-07',152,14,0),
(21,'ACC-07',25,17,0),
(22,'ACC-08',136,16,0),
(23,'ACC-08',189,19,0),
(24,'ACC-08',62,11,0),
(25,'ACC-09',173,10,0),
(26,'ACC-09',46,13,0),
(27,'ACC-09',99,16,0),
(28,'ACC-10',30,15,0),
(29,'ACC-10',83,18,0),
(30,'ACC-10',136,10,0),
(31,'ACC-11',67,9,0),
(32,'ACC-11',120,12,0),
(33,'ACC-11',173,15,0),
(34,'ACC-12',104,14,0),
(35,'ACC-12',157,17,0),
(36,'ACC-12',30,9,0),
(37,'ACC-13',141,19,0),
(38,'ACC-13',194,11,0),
(39,'ACC-13',67,14,0),
(40,'ACC-14',178,13,0),
(41,'ACC-14',51,16,0),
(42,'ACC-14',104,19,0),
(43,'ACC-15',35,18,0),
(44,'ACC-15',88,10,0),
(45,'ACC-15',141,13,0),
(46,'ACC-16',72,12,0),
(47,'ACC-16',125,15,0),
(48,'ACC-16',178,18,0),
(49,'ACC-17',109,17,0),
(50,'ACC-17',162,9,0),
(51,'ACC-17',35,12,0),
(52,'ACC-18',146,11,0),
(53,'ACC-18',199,14,0),
(54,'ACC-18',72,17,0),
(55,'ACC-19',183,16,0),
(56,'ACC-19',56,19,0),
(57,'ACC-19',109,11,0),
(58,'ACC-20',40,10,0),
(59,'ACC-20',93,13,0),
(60,'ACC-20',146,16,0),
(61,'ACC-21',77,15,0),
(62,'ACC-21',130,18,0),
(63,'ACC-21',183,10,0),
(64,'ACC-22',114,9,0),
(65,'ACC-22',167,12,0),
(66,'ACC-22',40,15,0),
(67,'ACC-23',151,14,0),
(68,'ACC-23',24,17,0),
(69,'ACC-23',77,9,0),
(70,'ACC-24',188,19,0),
(71,'ACC-24',61,11,0),
(72,'ACC-24',114,14,0),
(73,'ACC-25',45,13,0),
(74,'ACC-25',98,16,0),
(75,'ACC-25',151,19,0),
(76,'ACC-26',82,18,0),
(77,'ACC-26',135,10,0),
(78,'ACC-26',188,13,0),
(79,'ACC-27',119,12,0),
(80,'ACC-27',172,15,0),
(81,'ACC-27',45,18,0),
(82,'ACC-28',156,17,0),
(83,'ACC-28',29,9,0),
(84,'ACC-28',82,12,0),
(85,'ACC-29',193,11,0),
(86,'ACC-29',66,14,0),
(87,'ACC-29',119,17,0),
(88,'ACC-30',50,16,0),
(89,'ACC-30',103,19,0),
(90,'ACC-30',156,11,0),
(91,'ACC-31',87,10,0),
(92,'ACC-31',140,13,0),
(93,'ACC-31',193,16,0),
(94,'ACC-32',124,15,0),
(95,'ACC-32',177,18,0),
(96,'ACC-32',50,10,0),
(97,'ACC-33',161,9,0),
(98,'ACC-33',34,12,0),
(99,'ACC-33',87,15,0),
(100,'ACC-34',198,14,0),
(101,'ACC-34',71,17,0),
(102,'ACC-34',124,9,0),
(103,'ACC-35',55,19,0),
(104,'ACC-35',108,11,0),
(105,'ACC-35',161,14,0),
(106,'ACC-36',92,13,0),
(107,'ACC-36',145,16,0),
(108,'ACC-36',198,19,0),
(109,'ACC-37',129,18,0),
(110,'ACC-37',182,10,0),
(111,'ACC-37',55,13,0),
(112,'ACC-38',166,12,0),
(113,'ACC-38',39,15,0),
(114,'ACC-38',92,18,0),
(115,'ACC-39',23,17,0),
(116,'ACC-39',76,9,0),
(117,'ACC-39',129,12,0),
(118,'ACC-40',60,11,0),
(119,'ACC-40',113,14,0),
(120,'ACC-40',166,17,0),
(121,'ACC-33',2840,3,1),
(122,'ACC-33',3120,3,1),
(123,'ACC-33',2975,3,1);
Step 2: Train an anomaly model — no labels
CREATE EXPERIMENT recipe_ring_exp AS
SELECT amount, hour_of_day, cross_border
FROM recipe_ring_tx
WITH (
task_type = 'anomaly_detection',
algorithms = ['isolation_forest'],
max_trials = 5,
random_seed = 42
);
DEPLOY MODEL recipe_ring_model FROM EXPERIMENT recipe_ring_exp;
There is no target column: nobody has labelled these transactions, and nobody needs to. The
model learns the shape of normal traffic and scores distance from it. The result reports
score_basis: internal_validation — a separation measure, not an accuracy.
Step 3: Score every transaction with ANOMALY_SCORE() in a SELECT
SELECT account_id, amount, hour_of_day, cross_border,
ANOMALY_SCORE('recipe_ring_model', amount, hour_of_day, cross_border) AS score
FROM recipe_ring_tx
ORDER BY score DESC
LIMIT 10;
Expected: ACC-33's three payments at the top, scoring about 0.81–0.82. Below them the
scores drop straight to about 0.51–0.53 — ordinary customers. Higher means more unusual;
the score is a ranking, not a probability.
The feature columns must be passed in the order the model was trained on
(amount, hour_of_day, cross_border); a different order is refused with a clear
feature mismatch error rather than scored wrongly.
Step 4: Ask the model about the ring
SELECT account_id, amount, hour_of_day,
ANOMALY_SCORE('recipe_ring_model', amount, hour_of_day, cross_border) AS score
FROM recipe_ring_tx
WHERE account_id IN ('ACC-11', 'ACC-12', 'ACC-13', 'ACC-14', 'ACC-15')
ORDER BY score DESC;
Expected: 15 rows, all scoring between about 0.45 and 0.51 — exactly like honest customers. The model is not wrong: each of these payments is ordinary. A ring is not a property of any single transaction, so a per-transaction model cannot see it.
Step 5: Build the identity graph — accounts, devices, cards
Each account uses a device and pays with a card. Most have their own. The ring shares them
in a chain: ACC-11 and ACC-12 share a phone, ACC-12 and ACC-13 share a card, and so on.
ACC-21 and ACC-22 share a household tablet — honest, and unconnected to the ring.
MERGE (acc_11:Account {id: "ACC-11", src: "recipe-012"})
MERGE (dev_777:Device {id: "DEV-777", src: "recipe-012"})
MERGE (acc_12:Account {id: "ACC-12", src: "recipe-012"})
MERGE (card_4242:Card {id: "CARD-4242", src: "recipe-012"})
MERGE (acc_13:Account {id: "ACC-13", src: "recipe-012"})
MERGE (dev_778:Device {id: "DEV-778", src: "recipe-012"})
MERGE (acc_14:Account {id: "ACC-14", src: "recipe-012"})
MERGE (card_9001:Card {id: "CARD-9001", src: "recipe-012"})
MERGE (acc_15:Account {id: "ACC-15", src: "recipe-012"})
MERGE (acc_21:Account {id: "ACC-21", src: "recipe-012"})
MERGE (dev_500:Device {id: "DEV-500", src: "recipe-012"})
MERGE (card_1021:Card {id: "CARD-1021", src: "recipe-012"})
MERGE (acc_22:Account {id: "ACC-22", src: "recipe-012"})
MERGE (card_1022:Card {id: "CARD-1022", src: "recipe-012"})
MERGE (acc_33:Account {id: "ACC-33", src: "recipe-012"})
MERGE (dev_133:Device {id: "DEV-133", src: "recipe-012"})
MERGE (card_1033:Card {id: "CARD-1033", src: "recipe-012"})
MERGE (acc_07:Account {id: "ACC-07", src: "recipe-012"})
MERGE (dev_107:Device {id: "DEV-107", src: "recipe-012"})
MERGE (card_1007:Card {id: "CARD-1007", src: "recipe-012"})
MERGE (acc_36:Account {id: "ACC-36", src: "recipe-012"})
MERGE (dev_136:Device {id: "DEV-136", src: "recipe-012"})
MERGE (card_1036:Card {id: "CARD-1036", src: "recipe-012"})
MERGE (acc_11)-[:USES]->(dev_777)
MERGE (acc_12)-[:USES]->(dev_777)
MERGE (acc_12)-[:PAYS_WITH]->(card_4242)
MERGE (acc_13)-[:PAYS_WITH]->(card_4242)
MERGE (acc_13)-[:USES]->(dev_778)
MERGE (acc_14)-[:USES]->(dev_778)
MERGE (acc_14)-[:PAYS_WITH]->(card_9001)
MERGE (acc_15)-[:PAYS_WITH]->(card_9001)
MERGE (acc_21)-[:USES]->(dev_500)
MERGE (acc_21)-[:PAYS_WITH]->(card_1021)
MERGE (acc_22)-[:USES]->(dev_500)
MERGE (acc_22)-[:PAYS_WITH]->(card_1022)
MERGE (acc_33)-[:USES]->(dev_133)
MERGE (acc_33)-[:PAYS_WITH]->(card_1033)
MERGE (acc_07)-[:USES]->(dev_107)
MERGE (acc_07)-[:PAYS_WITH]->(card_1007)
MERGE (acc_36)-[:USES]->(dev_136)
MERGE (acc_36)-[:PAYS_WITH]->(card_1036);
Step 6: Start from one chargeback, walk the shared identifiers
ACC-13 has a confirmed chargeback. Find every account linked to it through shared devices or
cards, up to four hops away (account → identifier → account → identifier → account):
MATCH p = (s:Account {id: 'ACC-13'})-[*2..4]-(x:Account)
WHERE x.id <> 'ACC-13'
RETURN x.id AS linked_account, [n IN nodes(p) | n.id] AS via
ORDER BY length(p);
Expected: four accounts, each with the exact chain that links it:
| linked_account | via |
|---|---|
| ACC-14 | ACC-13 → DEV-778 → ACC-14 |
| ACC-12 | ACC-13 → CARD-4242 → ACC-12 |
| ACC-15 | ACC-13 → DEV-778 → ACC-14 → CARD-9001 → ACC-15 |
| ACC-11 | ACC-13 → CARD-4242 → ACC-12 → DEV-777 → ACC-11 |
The household pair (ACC-21, ACC-22) is not returned — sharing a device is not fraud; being
connected to a chargeback is. In SQL this would be a recursive CTE over a union of
account–device and account–card link tables; here it is one pattern.
Step 7: One review queue — ML and graph together
Save the graph result where SQL can join it:
DROP TABLE IF EXISTS recipe_ring_members;
CREATE TABLE recipe_ring_members (account_id TEXT PRIMARY KEY, linked_via TEXT);
INSERT INTO recipe_ring_members (account_id, linked_via) VALUES
('ACC-13', 'chargeback'),
('ACC-12', 'CARD-4242'),
('ACC-14', 'DEV-778'),
('ACC-11', 'CARD-4242 > DEV-777'),
('ACC-15', 'DEV-778 > CARD-9001');
Then rank every account by its most unusual payment, and keep anything the model or the graph flagged:
SELECT s.account_id, ROUND(s.max_anomaly, 2) AS max_anomaly, r.linked_via
FROM (
SELECT account_id, MAX(score) AS max_anomaly
FROM (
SELECT account_id,
ANOMALY_SCORE('recipe_ring_model', amount, hour_of_day, cross_border) AS score
FROM recipe_ring_tx
) t
GROUP BY account_id
) s
LEFT JOIN recipe_ring_members r ON r.account_id = s.account_id
WHERE s.max_anomaly > 0.7 OR r.account_id IS NOT NULL
ORDER BY s.max_anomaly DESC;
Expected: six accounts.
| account_id | max_anomaly | linked_via |
|---|---|---|
| ACC-33 | 0.82 | (null — caught by the model) |
| ACC-15 | 0.51 | DEV-778 > CARD-9001 |
| ACC-12 | 0.51 | CARD-4242 |
| ACC-13 | 0.49 | chargeback |
| ACC-14 | 0.48 | DEV-778 |
| ACC-11 | 0.48 | CARD-4242 > DEV-777 |
Read the max_anomaly column: five of the six would never have crossed a 0.7 threshold. The
model found one fraudster; the graph found five.
Cleanup (Optional)
DROP TABLE IF EXISTS recipe_ring_tx;
DROP TABLE IF EXISTS recipe_ring_members;
MATCH (n:Account {src: 'recipe-012'}) DETACH DELETE n;
MATCH (n:Device {src: 'recipe-012'}) DETACH DELETE n;
MATCH (n:Card {src: 'recipe-012'}) DETACH DELETE n;
Key Concepts Learned
- Per-transaction ML can't see rings. Each ring payment is ordinary; the fraud lives in the connections between accounts, not in any one row.
ANOMALY_SCORE()is a SELECT expression. No export, no scoring service; it works inORDER BYandWHERE. Pass features in training order.- Start from what you know. One confirmed chargeback plus a 2–4 hop traversal turns a single case into a whole ring, with the path that proves each link.
- Shared identifiers are not fraud on their own. The household pair shares a device but isn't connected to the chargeback, so it isn't flagged.
- One engine, both signals. The transactions, the model, the graph and the review queue live in the same database, so the queue is one query.