Synthetic AML transaction dataset with labelled typologies and ground truth

1. 1What precision and recall does a simple amount-threshold rule reach against the typology labels?Flagging amounts of $10,000 or more: precision 16.1%, recall 6.1%.

   with thresholds as (
     select 5000 as threshold union all select 9000 union all select 10000 union all select 50000
   )
   select t.threshold,
          sum(case when x.amount >= t.threshold then 1 else 0 end) as flagged,
          sum(case when x.amount >= t.threshold and x.is_suspicious then 1 else 0 end) as true_hits,
          round(100.0 * sum(case when x.amount >= t.threshold and x.is_suspicious then 1 else 0 end)
                / nullif(sum(case when x.amount >= t.threshold then 1 else 0 end), 0), 1) as precision_pct,
          round(100.0 * sum(case when x.amount >= t.threshold and x.is_suspicious then 1 else 0 end)
                / sum(case when x.is_suspicious then 1 else 0 end), 1) as recall_pct
   from thresholds t
   cross join transactions x
   group by t.threshold
   order by t.threshold

   threshold	flagged	true_hits	precision_pct	recall_pct
   5,000	927	135	14.6	18.6
   9,000	347	71	20.5	9.8
   10,000	274	44	16.1	6.1
   50,000	13	10	76.9	1.4

2. 2Which typology is hardest to catch with rules alone?layering is caught least: 10 of 12 customers using it drew a true alert.

   with launderers as (
     select distinct customer_id, typology
     from transactions
     where is_suspicious
   ),
   alerted as (
     select distinct customer_id from alerts where is_true_positive
   )
   select l.typology,
          count(*) as customers,
          sum(case when a.customer_id is not null then 1 else 0 end) as alerted,
          round(100.0 * sum(case when a.customer_id is not null then 1 else 0 end) / count(*), 1) as detected_pct
   from launderers l
   left join alerted a on a.customer_id = l.customer_id
   group by l.typology
   order by detected_pct, customers desc

   typology	customers	alerted	detected_pct
   layering	12	10	83.3
   mule_funnel	3	3	100
   structuring	3	3	100
   dormant_reactivation	2	2	100
   cash_commingling	2	2	100
   trade_based	1	1	100
   round_tripping	1	1	100

3. 3How many alerts does an investigator work for each true launderer found?84 alerts find 22 of the 24 customers in suspicious activity: 3.8 alerts for each one found.

   select count(*) as alerts,
          sum(case when is_true_positive then 1 else 0 end) as true_alerts,
          count(distinct case when is_true_positive then customer_id end) as launderers_found,
          (select count(distinct customer_id) from transactions where is_suspicious) as customers_in_suspicious_activity,
          round(1.0 * count(*) / count(distinct case when is_true_positive then customer_id end), 1) as alerts_per_launderer_found
   from alerts

   alerts	true_alerts	launderers_found	customers_in_suspicious_activity	alerts_per_launderer_found
   84	50	22	24	3.8

4. 4Do structured cash deposits cluster in the days before a case opens?Structured deposits by when they fell against their customer's case: 8-30 days before 25, 0-7 days before 25, after the case opened 10.

   with d as (
     select extract(epoch from (c.opened_at - t.txn_at)) / 86400 as days_before
     from transactions t
     join cases c on c.customer_id = t.customer_id
     where t.typology = 'structuring'
   )
   select case when days_before < 0 then 'after the case opened'
               when days_before <= 7 then '0-7 days before'
               when days_before <= 30 then '8-30 days before'
               when days_before <= 90 then '31-90 days before'
               else 'over 90 days before' end as timing,
          count(*) as deposits
   from d
   group by 1
   order by min(days_before) desc

   timing	deposits
   8-30 days before	25
   0-7 days before	25
   after the case opened	10

Every query runs on the files in the download as they are. ANSWERS.sql holds all of them; run it in DuckDB from the dataset's folder.

• Credit risk portfolio3,000 borrowers and 4,200 commercial loans, where a credit rating actually predicts whether the loan defaults, at the rate S&P's own data says it should.7,200 rows · 2 tables
• Factory sensors with failure labelsA year of hourly sensor readings from six machines, with the degradation stage, remaining useful life, and the failures and work orders they lead to.52,669 rows · 5 tables
• Retail star schemaA dimensional model you can actually practise joins on: one fact table, four dimensions, 47,051 rows, every join resolving and every total known.47,051 rows · 5 tables
• Machine degradation100 machines monitored from commissioning to failure, with an exact remaining-useful-life label on every one of 23,118 readings.46,336 rows · 3 tables
• Ecommerce storefrontA year of orders across five joined tables, with best sellers, heavy buyers, a real Q4 peak and totals that reconcile to the cent.49,493 rows · 5 tables
• B2B SaaS subscription analyticsAccounts, seats, MRR, churn and support load, where company size actually drives the plan.42,240 rows · 5 tables