Data Science / ML · Case study 07

Customer Spending & Risk Analytics

Heavy aggregation pushed into the database, Python kept to the insight layer — the way real analytics stacks work.

The problem

Banks generate millions of transactions. Buried in them: who the most valuable customers are, whose spending is behaving abnormally, and which customers are genuinely risky.

The build

Advanced PostgreSQL — CTEs, window functions, per-customer anomaly detection relative to that customer's own baseline — with a thin pandas layer for visualisation and a transparent composite risk score. The design deliberately keeps aggregation close to the data.

Architecture

— one teal highlight: the per-customer baseline

Source

Transactionsrealistic bank schema

Customer masterdimension

Aggregate

PostgreSQL CTEsheavy lifting in-database

Window functionsper-customer frames

Score

Per-customer baselineown history, not the crowd

Composite risk scoreevery component explainable

Rank

Value · anomaly · riskthree outputs

Present

pandas + matplotlibthin insight layer

What I found

Anomaly thresholds set against the population miss the customer whose 'normal' is unusual. Scoring each customer against their own history is what turns a transaction table into a fraud signal.

The reps

Making the risk score transparent — every component explainable to a risk officer — mattered more than making it optimal.

SQL-first
aggregation in Postgres, not pandas
3
outputs: value, anomaly, risk rank
1
executed notebook end to end

What's verified

Every case study carries this block

What was measured, and what was not. Nothing in the second column is estimated into the first.

Measured

The notebook is committed already-executed against the schema in the repo.

Not executed

Transaction data is simulated to a realistic banking schema.

PostgreSQLAdvanced SQLPythonpandasmatplotlib

View repository →