Account Takeover Pattern
Stripe
Problem Statement
Account Takeover (ATO) is a major threat in fintech. A classic pattern involves a fraudster gaining access, changing the email to lock out the victim, changing the password, and immediately draining the account balance.
Write a query to detect users who performed an email_change, a password_change, and a withdrawal all within a single 60-minute window.
Rules:
- Output columns: user_id, pattern_start, pattern_end.
- pattern_start is the timestamp of the first event in the 1-hour sequence.
- pattern_end is the timestamp of the final event (the withdrawal) in that sequence.
- All three events must be distinct and occur within 60 minutes of each other.
- Result must be sorted by user_id ascending, then by pattern_start ascending.
Tests your understanding of
Self Join, Time Series and Security Audit
Input Tables
account_events
| event_id(INTEGER) | user_id(INTEGER) | event_type(VARCHAR) | event_timestamp(TIMESTAMP) |
|---|---|---|---|
| 1 | 101 | email_change | 2024-06-01 10:00:00 |
| 2 | 101 | password_change | 2024-06-01 10:15:00 |
| 3 | 101 | withdrawal | 2024-06-01 10:45:00 |
| 4 | 102 | email_change | 2024-06-01 12:00:00 |
| 5 | 102 | withdrawal | 2024-06-01 12:30:00 |
| 6 | 103 | email_change | 2024-06-01 14:00:00 |
| 7 | 103 | password_change | 2024-06-01 15:30:00 |
| 8 | 103 | withdrawal | 2024-06-01 15:45:00 |
Expected Output
| user_id(INTEGER) | pattern_start(TIMESTAMP) | pattern_end(TIMESTAMP) |
|---|---|---|
| 101 | 2024-06-01 10:00:00 | 2024-06-01 10:45:00 |
Tags
HardSelf JoinTime SeriesSecurity Audit
45-55 min
22%