Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Razorpay
Detect fraudulent transaction patterns using velocity analysis (transaction frequency and amount anomalies within time windows).
Razorpay's risk team needs an automated first line of defense against card-testing bursts and runaway spend: too many small rapid-fire transactions (a fraudster probing a stolen card) or an abnormal hourly total should freeze the card before the money moves, not after the chargeback.
The screen works on velocity: for every transaction, count peer transactions by the same user in the trailing 10 minutes and sum amounts over the trailing 60 minutes, then flag HIGH_FREQUENCY (5+ in 10 minutes) or HIGH_VOLUME (over ₹500K in an hour) as FLAGGED, with an UNUSUAL_AMOUNT indicator (over 2.5× the global average) recorded for context. Output is the review queue, oldest first.
Then comes the uncomfortable part — on this dataset the screen fires on nothing. A clean sheet can mean a clean book or blind rules, and the data suggests the latter: the busiest burst peaks at 3-in-10-minutes against a 5-transaction rule. Say it plainly, propose the tighter threshold, and note the dead logic (the amount indicator can never trigger a flag as written).
Transaction logs with user_id, card_id, amount, transaction_timestamp, and merchant_category columns. We need to detect users exceeding normal patterns (e.g., 5+ transactions in 10 minutes, or >₹500K in 1 hour).
CREATE TABLE transactions (
transaction_id INTEGER PRIMARY KEY,
user_id INTEGER,
card_id INTEGER,
amount DECIMAL(10,2),
transaction_timestamp DATETIME,
merchant_category TEXT
);Your target
Detect fraudulent transaction patterns using velocity analysis (transaction frequency and amount anomalies within time windows).
Loading the expected output…
WITH transaction_windows AS (
SELECT
t1.user_id,
t1.card_id,
t1.transaction_timestamp,
t1.amount,
(SELECT COUNT(*) FROM transactions t2
WHERE t2.user_id = t1.user_id
AND t2.transaction_timestamp <= t1.transaction_timestamp
AND t2.transaction_timestamp > datetime(t1.transaction_timestamp, '-10 minutes')
) as txn_count_10min,
(SELECT SUM(t2.amount) FROM transactions t2
WHERE t2.user_id = t1.user_id
AND t2.transaction_timestamp <= t1.transaction_timestamp
AND t2.transaction_timestamp > datetime(t1.transaction_timestamp, '-60 minutes')
) as amount_sum_60min,
ROW_NUMBER() OVER (PARTITION BY t1.user_id ORDER BY t1.transaction_timestamp) as txn_sequence
FROM transactions t1
),
velocity_flags AS (
SELECT
user_id,
card_id,
transaction_timestamp,
amount,
txn_count_10min,
amount_sum_60min,
CASE
WHEN txn_count_10min >= 5 THEN 'HIGH_FREQUENCY'
WHEN amount_sum_60min > 500000 THEN 'HIGH_VOLUME'
WHEN amount > (SELECT AVG(amount) * 2.5 FROM transactions) THEN 'UNUSUAL_AMOUNT'
ELSE 'NORMAL'
END as risk_indicator,
CASE
WHEN txn_count_10min >= 5 OR amount_sum_60min > 500000 THEN 'FLAGGED'
ELSE 'CLEAN'
END as fraud_flag
FROM transaction_windows
)
SELECT
user_id,
card_id,
transaction_timestamp,
amount,
txn_count_10min,
amount_sum_60min,
risk_indicator,
fraud_flag
FROM velocity_flags
WHERE fraud_flag = 'FLAGGED'
ORDER BY user_id, transaction_timestamp;
Step-by-step Solution:
Create a transaction_windows CTE to calculate rolling windows using RANGE BETWEEN clauses. We count transactions in the last 10 minutes (txn_count_10min) and sum amounts in the last 60 minutes (amount_sum_60min) for each user.
Use window functions partitioned by user_id with ORDER BY transaction_timestamp to respect chronological order.
In velocity_flags CTE, we apply business rules: flag if 5+ transactions in 10 mins (card cloning), or total amount >₹500K in 1 hour (account takeover), or individual transaction >2.5x average (unusual amount).
Filter for FLAGGED records to show only high-risk transactions. This approach catches fraud patterns before they escalate, enabling real-time intervention.
Computing the reference output…
Clean sheet: no transaction breaches the velocity rules on this day's data. But that is a threshold problem as much as a fraud problem — the busiest burst (user 101: 6 transactions in 18 minutes) peaks at 3-in-10-minutes against a 5-transaction rule, and peak hourly spend (Rs.13.3K) sits far below the Rs.500K bar. Tighten the frequency rule toward 3-in-10-minutes or this screen will keep catching nothing.
What the interviewer asks next
“Your screen flags zero transactions — clean book, or blind rules? How do you tell the difference?”
Strong answer covers: Backtesting discipline: replay the rules against historical confirmed fraud; a screen that has never fired has unknown recall, and unknown recall is not safety.
“UNUSUAL_AMOUNT is computed but can never trigger a flag — dead logic sitting in production. What now?”
Strong answer covers: Code-reader's eye: the flag condition only checks frequency and volume, so the amount signal is decorative — either wire it into the flag or delete it, and say which.
“User 101 fires 6 transactions in 18 minutes — block the card, step up authentication, or watch?”
Strong answer covers: Action design under uncertainty: weigh false-positive cost (angry good customer) against fraud loss; step-up auth on burst patterns is the calibrated middle most risk teams pick.
“User 103 transacts at 09:00, 10:00, 10:30, 11:00 — metronomic hourly cadence totaling Rs.19K. Human shopping or bot script? Your velocity rules say clean.”
Strong answer covers: Cadence as signal: perfectly spaced transactions evade count-and-sum rules entirely — regularity detection (variance of inter-transaction gaps) catches what velocity misses.
“User 101 hits Retail, Grocery, Fuel, and Online within 18 minutes — does cross-merchant spread strengthen or weaken the fraud hypothesis?”
Strong answer covers: Behavioral reading: legitimate errands cluster by category and geography; a card-testing script sprays categories to find what authorizes — merchant entropy deserves its own feature.
“Each user here owns exactly one card (101→5001, 102→5002…). What breaks in your flags the day cards are shared — family plans, corporate cards?”
Strong answer covers: Entity-resolution fragility: velocity keyed on user_id misattributes shared-card bursts — design the flag on card_id with user-level rollups, and state the false-positive cost of getting it wrong.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.