Factory sensors with failure labels

1. 1How early can a model flag a failure using vibration and temperature alone?Vibration averages 2.57 mm/s over a week out and 4.45 mm/s in the last 24 hours.

   -- Running readings only, by hours left before the next failure.
   select case when rul_hours <= 24 then '0-24 h'
               when rul_hours <= 72 then '25-72 h'
               when rul_hours <= 168 then '73-168 h'
               else 'over 168 h' end as hours_to_failure,
          count(*) as readings,
          round(avg(vibration_mm_s), 2) as avg_vibration_mm_s,
          round(avg(bearing_temp_c), 1) as avg_bearing_temp_c
   from sensor_readings
   where operating_mode <> 'stopped' and rul_hours is not null
   group by 1
   order by min(rul_hours)

   hours_to_failure	readings	avg_vibration_mm_s	avg_bearing_temp_c
   0-24 h	897	4.45	62.2
   25-72 h	1,868	4.21	60.9
   73-168 h	3,735	3.87	59.5
   over 168 h	39,942	2.57	53.5

2. 2Does remaining useful life fall faster for some failure modes?seal_leak comes soonest after a repair (1,202 h on average), bearing_wear latest (1,340 h).

   select failure_mode,
          count(*) as failures,
          round(avg(hours_since_last_repair), 0) as avg_hours_between_failures,
          round(min(hours_since_last_repair), 0) as shortest,
          round(max(hours_since_last_repair), 0) as longest
   from failure_events
   where hours_since_last_repair is not null
   group by failure_mode
   order by avg_hours_between_failures

   failure_mode	failures	avg_hours_between_failures	shortest	longest
   seal_leak	11	1,202	569	1,960
   imbalance	4	1,208	799	1,529
   electrical_fault	7	1,281	940	1,818
   overheating	6	1,311	283	3,161
   bearing_wear	11	1,340	598	2,785

3. 3How much downtime does each failure mode cost?bearing_wear costs the most downtime: 119 hours over 11 failures.

   select failure_mode,
          count(*) as failures,
          round(sum(downtime_hours), 1) as downtime_hours,
          round(avg(downtime_hours), 1) as avg_downtime_hours
   from failure_events
   group by failure_mode
   order by downtime_hours desc

   failure_mode	failures	downtime_hours	avg_downtime_hours
   bearing_wear	11	119	10.8
   seal_leak	11	114.1	10.4
   overheating	6	92.2	15.4
   electrical_fault	7	68.3	9.8
   imbalance	4	47.7	11.9

4. 4Are preventive work orders placed before or after degradation begins?Preventive work orders opened while the machine was healthy (13), degrading (6), critical (5).

   -- The machine's state in the hour before each work order was opened (the work order itself stops it).
   select w.order_type, r.degradation_stage, count(*) as work_orders
   from work_orders w
   join sensor_readings r
     on r.asset_id = w.asset_id and r.recorded_at = date_trunc('hour', w.opened_at) - interval '1 hour'
   group by w.order_type, r.degradation_stage
   order by w.order_type, work_orders desc

   order_type	degradation_stage	work_orders
   corrective	stopped	34
   corrective	critical	5
   preventive	healthy	13
   preventive	degrading	6
   preventive	critical	5

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.

• 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
• Synthetic AML transaction dataset with labelled typologies and ground truthA bank's customers, accounts and transactions, with the laundering typologies labelled, the rule-based alerts raised, and the cases and SARs that followed.44,612 rows · 6 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
• 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
• 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