Why should you care?

Fraud model needs customer order count last 7 days as feature — SQL aggregate feeds ML pipeline nightly and near-real-time.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE ml_features (
  customer_id INT UNSIGNED PRIMARY KEY,
  orders_7d INT NOT NULL DEFAULT 0,
  avg_order_inr DECIMAL(10,2),
  last_order_at DATETIME,
  feature_json JSON
);
INSERT INTO ml_features (customer_id, orders_7d, avg_order_inr, last_order_at)
SELECT customer_id,
       COUNT(*),
       AVG(total_inr),
       MAX(placed_at)
FROM orders
WHERE placed_at >= NOW() - INTERVAL 7 DAY
GROUP BY customer_id
ON DUPLICATE KEY UPDATE
  orders_7d = VALUES(orders_7d),
  avg_order_inr = VALUES(avg_order_inr),
  last_order_at = VALUES(last_order_at);

What happened?

• Batch job refreshes feature table from orders.
• JSON holds experimental features.
• Model service SELECT by customer_id at inference time.

Practice next

1. Create ml_features table.
2. Run INSERT SELECT from orders.
3. Point Python sklearn script to read mysql table.
4. Add model_version and computed_at columns.
5. Export SELECT to CSV with INTO OUTFILE (where permitted).

Remember

SQL aggregates produce ML features. Feature table refreshed on schedule. JSON for flexible experimental attrs.

DataFlow fraud score

Checkout calls model with orders_7d from ml_features refreshed hourly.

Outcome: Blocked cards drop without manual rules only.