לעקוב אחרי הכסף

מעקב אחרי כסף שמועבר בין משתמשים החשודים בהלבנת הון

מתקדם
Published

May 4, 2024

ברוכים הבאים לתרגיל השמיני!

גם התרגיל הזה (בדומה לקודם) נלקח מתוך הרצאה שהעברתי על מה זה להיות דאטה אנליסט בתחום ההונאות :)

שאלה

אתם דאטה אנליסטים בתחום ההונאות ומצאתם משתמש שחשוד בהלבנת כספים, משתמש מספר 7804.

הלבנת כספים כוללת שלושה שלבים: השמה, ריבוד והטמעה. המשתמש כבר הכניס את הכסף למערכת ועכשיו אתם רוצים לחקות אחריו ולמפות את הרשת שלוקחת חלק בתהליך הריבוד.

כלומר, בהינתן שהתשלום הראשון ברשת שמעניינת אותנו התרחש ממשתמש 7804, ואת זה הוא שלח למישהו ששלח למישהו וכן הלאה, מי היה היוזר האחרון שקיבל את הסכום משרשרת ההעברות?

צרו פלט שמראה את כל שרשרת ההעברות מהיוזר הראשון החשוד, 7804, ועד היוזר האחרון שקיבל את הכסף.

הדאטה

התרגיל מורכב מתשלומים בין יוזרים שונים:

SELECT TOP 5 * 
FROM payments_8;
5 records
payment_id from_user to_user amount payment_date
1 7804 4360 200 2023-02-28
2 1237 5790 242 2023-02-28
3 5363 7800 241 2023-02-28
4 6382 5619 224 2023-02-28
5 7962 2729 184 2023-02-28

בנוסף למשתמש שמעניין אותנו (7804) יש מספר רב של תשלומים לגיטימיים שלא רלוונטים לרשת שאנחנו מחפשים.

נסו בעצמכם

אונליין

נסו בחלון טקסט למטה (SQLite):

-- EDIT THIS QUERY:
SELECT *
FROM payments_8
LIMIT 10;

נסו על המחשב שלכם

הצג קוד ליצירת טבלה 
CREATE TABLE payments_8 
(
    payment_id  INT,
    from_user   INT,
    to_user INT,
    amount  INT,
    payment_date    DATE
);

INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('1', '7804', '4360', '200', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('2', '1237', '5790', '242', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('3', '5363', '7800', '241', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('4', '6382', '5619', '224', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('5', '7962', '2729', '184', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('6', '6165', '5363', '170', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('7', '2165', '2502', '198', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('8', '5867', '8562', '285', '2023-02-28');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('9', '2021', '4386', '253', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('10', '1378', '6818', '264', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('11', '4360', '7503', '199', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('12', '4192', '6165', '223', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('13', '7764', '7226', '226', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('14', '5517', '7962', '294', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('15', '1150', '2021', '297', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('16', '2502', '6816', '262', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('17', '9990', '2625', '177', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('18', '7503', '6491', '198', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('19', '547', '7800', '283', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('20', '355', '976', '212', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('21', '3113', '928', '172', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('22', '4056', '7498', '155', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('23', '2508', '5867', '286', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('24', '2135', '672', '280', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('25', '4241', '4386', '245', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('26', '2587', '5517', '184', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('27', '6491', '3037', '198', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('28', '9003', '3113', '169', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('29', '6675', '3440', '279', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('30', '8193', '976', '189', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('31', '4463', '1657', '279', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('32', '1416', '8562', '181', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('33', '3037', '9484', '198', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('34', '5669', '6286', '267', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('35', '8889', '928', '153', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('36', '4915', '3113', '284', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('37', '8319', '9249', '240', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('38', '4400', '9990', '204', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('39', '9492', '9816', '265', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('40', '9484', '2885', '197', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('41', '870', '9207', '259', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('42', '5948', '4896', '260', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('43', '6818', '7949', '150', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('44', '6765', '547', '216', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('45', '2885', '1841', '196', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('46', '392', '6862', '287', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('47', '5067', '7869', '253', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('48', '2729', '5948', '244', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('49', '9249', '6382', '208', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('50', '258', '4241', '274', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('51', '1841', '7627', '196', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('52', '1408', '7520', '281', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('53', '9725', '8889', '221', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('54', '3900', '1101', '178', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('55', '657', '2587', '150', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('56', '6862', '4463', '278', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('57', '7627', '5923', '196', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('58', '2700', '9207', '150', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('59', '7687', '1408', '295', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('60', '4719', '1733', '272', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('61', '5923', '9464', '195', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('62', '1101', '5517', '259', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('63', '4478', '6286', '172', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('64', '6313', '9098', '186', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('65', '4426', '1867', '275', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('66', '7226', '4386', '255', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('67', '9464', '781', '194', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('68', '2625', '500', '162', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('69', '2318', '4937', '213', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('70', '781', '7364', '194', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('71', '9962', '2165', '212', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('72', '1867', '9098', '200', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('73', '2381', '4426', '233', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('74', '4200', '2021', '211', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('75', '954', '3440', '274', '2023-03-01');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('76', '7364', '191', '194', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('77', '672', '2165', '236', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('78', '191', '1577', '193', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('79', '1094', '3113', '251', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('80', '1133', '1416', '203', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('81', '500', '3669', '242', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('82', '8074', '3302', '180', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('83', '163', '2502', '286', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('84', '1577', '2921', '192', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('85', '7558', '2502', '187', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('86', '180', '7241', '168', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('87', '2921', '6093', '192', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('88', '5790', '9003', '231', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('89', '4247', '552', '216', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('90', '7449', '2165', '171', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('91', '7869', '4400', '258', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('92', '4111', '8474', '176', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('93', '6093', '3423', '192', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('94', '5013', '1733', '176', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('95', '592', '8319', '225', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('96', '4386', '4192', '254', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('97', '4937', '258', '209', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('98', '8647', '1657', '194', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('99', '3423', '5649', '191', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('100', '928', '7791', '169', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('101', '5649', '1721', '190', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('102', '7241', '7687', '226', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('103', '6914', '7687', '202', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('104', '6816', '1867', '291', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('105', '1721', 'NA', '190', '2023-03-02');
INSERT INTO payments_8 (payment_id, from_user, to_user, amount, payment_date) VALUES ('106', '7498', '1657', '221', '2023-03-02');

תשובות

סרטון הסבר

תשובה

הצג פתרון

(מראה רק את ה-10 שורות הראשונות)

WITH recursive_payments  as 
(
SELECT from_user,
    to_user,
    amount,
    1 as Iteration
FROM payments_8
WHERE from_user = 7804
UNION ALL
SELECT ml.from_user,
    ml.to_user,
    ml.amount,
    Iteration + 1 as Iteration
FROM recursive_payments rp
JOIN payments_8 ml on ml.from_user = rp.to_user
where Iteration <= 20
)

SELECT * 
FROM recursive_payments
Displaying records 1 - 10
from_user to_user amount Iteration
7804 4360 200 1
4360 7503 199 2
7503 6491 198 3
6491 3037 198 4
3037 9484 198 5
9484 2885 197 6
2885 1841 196 7
1841 7627 196 8
7627 5923 196 9
5923 9464 195 10