Table Schema
Inspect
Table
| Column | Type |
|---|---|
| txn_id | integer |
| user_id | integer |
| amount | decimal |
| txn_time | datetime |
| device_id | varchar |
Fraud
Detection
Find users who made more than 3 transactions within 1 minute.
Inspect
Sample Data
Input
Output
Sample Input: transactions
| txn_id | user_id | amount | txn_time | device_id |
|---|---|---|---|---|
| 1 | 101 | 100 | 10:00:00 | A |
| 2 | 101 | 200 | 10:00:20 | A |
| 3 | 101 | 150 | 10:00:40 | A |
| 4 | 101 | 300 | 10:00:50 | A |
| 5 | 102 | 500 | 11:00:00 | B |
| 6 | 102 | 600 | 11:05:00 | B |
| 7 | 103 | 200 | 12:00:00 | C |
Expected Output
| user_id |
|---|
| 101 |
SQL Editor
Run
Query
Waiting for query
| user_id |
|---|
| 101 |
Hints
Unlock
Clues
Hint 01: Treat each transaction as a possible window start.
Hint 02: Join later transactions within 1 minute for the same user.
Hint 03: Keep windows where the count is greater than 3.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT DISTINCT t1.user_id FROM transactions t1 JOIN transactions t2 ON t1.user_id = t2.user_id AND t2.txn_time BETWEEN t1.txn_time AND t1.txn_time + INTERVAL '1 minute' GROUP BY t1.user_id, t1.txn_id, t1.txn_time HAVING COUNT(*) > 3;
Explanation
Step By
Step
01
Self-join transactions by user.
02
Limit the joined rows to a 1 minute window.
03
Return users with more than 3 transactions in any such window.
Related Questions