Fraud: IP Velocity
Pay Pal, Stripe, Adyen
Problem Statement
Coordinated fraud often involves a single bad actor using a proxy or a compromised server to access dozens of different accounts. PayPal monitors "IP Velocity" to flag these suspicious nodes for manual review.
Write a query to identify any IP address that was used by more than 10 different Account IDs in a single day.
Rules:
- A "day" is defined by the date part of the login_timestamp.
- You must count unique account_id values per IP per day.
- Only return records where the unique account count is strictly greater than 10.
- Output columns: ip_address, login_date, unique_account_count.
- Sort the results by unique_account_count descending, then login_date ascending.
Tests your understanding of
Distinct Count, Grouping, Date Truncation and Filtering
Input Tables
login_attempts
| attempt_id(INTEGER) | account_id(INTEGER) | ip_address(VARCHAR) | login_timestamp(TIMESTAMP) |
|---|---|---|---|
| 1 | 101 | 192.168.1.1 | 2026-03-04 08:00:00 |
| 2 | 102 | 192.168.1.1 | 2026-03-04 08:05:00 |
| 3 | 103 | 192.168.1.1 | 2026-03-04 08:10:00 |
| 4 | 104 | 192.168.1.1 | 2026-03-04 08:15:00 |
| 5 | 105 | 192.168.1.1 | 2026-03-04 08:20:00 |
| 6 | 106 | 192.168.1.1 | 2026-03-04 08:25:00 |
| 7 | 107 | 192.168.1.1 | 2026-03-04 08:30:00 |
| 8 | 108 | 192.168.1.1 | 2026-03-04 08:35:00 |
| 9 | 109 | 192.168.1.1 | 2026-03-04 08:40:00 |
| 10 | 110 | 192.168.1.1 | 2026-03-04 08:45:00 |
| 11 | 111 | 192.168.1.1 | 2026-03-04 08:50:00 |
| 12 | 112 | 10.0.0.5 | 2026-03-04 09:00:00 |
Expected Output
| ip_address(VARCHAR) | login_date(DATE) | unique_account_count(INTEGER) |
|---|---|---|
| 192.168.1.1 | 2026-03-04 | 11 |
Tags
MediumDistinct CountGroupingDate TruncationFiltering
15-25 min
38%