Intermediate4 weeks

UPI transaction risk analytics

Spot unusual payments with SQL window functions and show them on a simple report.

Tools: SQL, DuckDB or PostgreSQL, Python, Streamlit or Metabase

Why interviewers like it

Payments are a topic every interviewer understands, and the project uses window functions on a problem where they are the natural answer. You can explain it in two minutes and defend it for twenty.

The problem

A payments app sees thousands of transactions a minute. A few look odd: many payments in a few minutes, or one amount far above what that person usually pays. You will generate realistic transactions, flag the odd ones with clear rules, and report how many were flagged each day.

How the data moves

  1. Generated payments
  2. Load
  3. Rule features (SQL)
  4. Flags table
  5. Daily report

The data

Generate your own payments with a Python script (transaction id, user, amount, time). Never use real payment data. Add a few planted cases, such as five payments in five minutes, so you can check that your rules catch them.

Week by week

  1. Week 1Write the generator with normal users and a few planted odd cases. Load it to a table.
  2. Week 2Write the rules as SQL: payments in the last 10 minutes, amount compared with the user's average.
  3. Week 3Store flags in a table with the reason. Measure how many planted cases you catch and how many normal payments you flag by mistake.
  4. Week 4Build a daily report and write the README, including what the rules cannot catch.

Starter code

Two risk signals with window functions. Run it first, then grow it into the real project.

SQLTwo risk signals with window functions
CREATE TABLE txns (txn_id INTEGER, user_id VARCHAR, amount DOUBLE, ts TIMESTAMP);
INSERT INTO txns VALUES
(1,'u1',200,TIMESTAMP '2024-03-01 10:00:00'),
(2,'u1',250,TIMESTAMP '2024-03-01 10:02:00'),
(3,'u1',300,TIMESTAMP '2024-03-01 10:04:00'),
(4,'u1',280,TIMESTAMP '2024-03-01 10:06:00'),
(5,'u2',150,TIMESTAMP '2024-03-01 11:00:00'),
(6,'u2',9000,TIMESTAMP '2024-03-01 15:00:00');
SELECT txn_id, user_id, amount,
  COUNT(*) OVER (PARTITION BY user_id ORDER BY ts RANGE BETWEEN INTERVAL 10 MINUTES PRECEDING AND CURRENT ROW) AS txns_in_10_min,
  ROUND(amount / AVG(amount) OVER (PARTITION BY user_id), 2) AS times_user_average
FROM txns ORDER BY txn_id;
txn_id | txns_in_10_min | times_user_average
1      | 1              | 0.78
2      | 2              | 0.97
3      | 3              | 1.17
4      | 4              | 1.09
5      | 1              | 0.03
6      | 1              | 1.97

Payment 4 is the fourth in ten minutes, which is a burst. The average includes the current payment, which is why the 9000 payment shows only 1.97 times: with so little history, one huge payment pulls the average up. Compare against the user's earlier payments to fix this.

What to hand in

  • A generator script and the SQL for each rule
  • A flags table with a reason for each flagged payment
  • A short table of how many planted cases were caught and how many normal payments were flagged
  • A daily report and a README that says honestly what the rules miss

Viva and interview questions

Practise saying each answer out loud. Short and honest beats long and vague.

Why window functions and not GROUP BY?

GROUP BY collapses rows. I needed every payment kept, with a number computed from the payments around it. A window function does exactly that.

How do you know your rules are any good?

I planted cases I knew were odd and counted how many were caught. I also counted normal payments that were flagged by mistake, because too many false alarms make a rule useless.

What is wrong with comparing to the user's average including the current payment?

A very large payment raises the average it is compared with, which hides it. Comparing with earlier payments only is fairer, and I noted this in the README.

Is this machine learning?

No. These are simple, explainable rules. That is a strength: an analyst can see exactly why a payment was flagged. A model could be a next step.

How would it work in real time?

The same rules would run on a stream, keeping a small recent history per user. For this project I used batch runs to keep the focus on the rules and the checks.

How to stand out

  • Add a third rule, such as a payment at an unusual hour for that user
  • Report precision: of all flagged payments, how many were truly planted cases
  • Let the thresholds live in a config table and show how changing them changes the flags

Put it on your CV

OutputResume bullet
Built a payment risk analytics pipeline in SQL that flags bursts of transactions and unusually large amounts using window functions, evaluated against planted cases, with a daily flag report.

README outline

  1. The problem
  2. Data (generated, not real)
  3. The rules and why
  4. How I tested them
  5. What the rules miss
  6. How to run it

Learn the pieces