Design Fraud Detection System | Dataford Interview Questions

How would you design a detection system for fraudulent payment transactions at ByteDance?

Using the provided payment and account data, write a query that identifies completed payments meeting at least one fraud rule: high-risk account, unusually large amount, billing-country mismatch, missing billing country, or more than two completed payments by the same account within 24 hours.

Output

One row per suspicious payment with payment_id, account_id, amount, payment_ts, risk_reason, and payments_24h
Include only suspicious completed payments linked to an account
Order by payment_ts ascending, then payment_id ascending

Schema

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
account_id	INT	Account that initiated the payment
amount	DECIMAL(10,2)	Payment amount
payment_ts	TIMESTAMP	Payment creation timestamp
billing_country	VARCHAR(2)	Country supplied for billing
status	VARCHAR(20)	Payment processing status

accounts

Column	Type	Description
account_idPK	INT	Unique customer account identifier
account_country	VARCHAR(2)	Country registered on the account
risk_tier	VARCHAR(10)	Account risk classification

Your question is Design Fraud Detection System. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem