🚨 ALERT: Suspicious Activity Detected.
It is 2:47 AM. The fraud operations center at Razorpay is lit up like a Christmas tree. The automated systems flagged something, but the details are murky. Your phone rings. It is the Head of Risk.
'We think there's a fraud ring operating through our platform. Multiple accounts, same device fingerprints, suspiciously high transaction velocity. I need a query in 30 minutes that identifies every account that processed more than 5 transactions in any single hour, with a total value exceeding ₹50,000 in that window. This is evidence. It needs to be airtight.'
This is the hardest type of SQL problem: a sliding window aggregation combined with a self-join to find temporal clusters. You need to find, for each transaction, all other transactions by the same account within the following 60 minutes — then check if that cluster exceeds the thresholds.
Your task: Find all account_ids where there exists at least one 1-hour window containing more than 5 transactions totalling over 50000. Return account_id, window_start (the earliest txn time in the window), txn_count, and window_total. Order by window_total descending.
transactions Table:
| Column Name | Type |
|---|---|
| txn_id | integer |
| account_id | integer |
| amount | integer |
| txn_time | text (ISO datetime) |
| merchant | text |
Expected Outcome (Illustrative Sample):
| account_id | window_start | txn_count | window_total |
|---|---|---|---|
| 1042 | 2024-03-15 02:10:00 | 7 | 87500 |
The evidence window closes at dawn. Write the query.