Loading...
Loading...
You are a data engineer at a fintech company tasked with building a fraud detection system. Your job is to write a SQL query that identifies customers who exhibit suspicious transaction patterns — specifically, customers who have made 3 or more transactions each exceeding $1,000 within any 30-day rolling window.
You have a single table: transactions
| Column | Type | Description |
|---|---|---|
transaction_id | INT | Unique identifier for each transaction |
customer_id | INT | Identifier for the customer |
amount | DECIMAL(10,2) | Transaction amount in USD |
transaction_date | DATE | Date of the transaction |
Write a SQL query to return all customer_ids that have at least 3 transactions over $1,000 where at least 3 of those transactions fall within a 30-day window (i.e., the date difference between the earliest and latest of those 3 transactions is ≤ 30 days).
Return the result as a list of distinct customer_ids sorted in ascending order.
Input: The transactions table as described above (provided as rows).
Output: A result set with a single column customer_id, listing all customers flagged as suspicious.
If customer 101 has transactions:
All three are over $1,000 and span 24 days → flagged.
If customer 102 has transactions:
All three are over $1,000 but each pair is more than 30 days apart → not flagged (no 3 within 30-day window).
If customer 103 has transactions:
Three of the four transactions exceed $1,000 and are within a 14-day window → flagged.
transactions table to itself on customer_id to find triplets where all amounts exceed $1,000 and the date range is ≤ 30 days.1 <= number of rows in transactions <= 10^5 1 <= customer_id <= 10^6 0.01 <= amount <= 10^6 transaction_date is a valid DATE in format 'YYYY-MM-DD' No duplicate transaction_ids A customer may have 0 to 1000 transactions
ROW_NUMBER() or LAG()/LEAD() over partitioned windows to detect clusters.amount > 1000 before checking the 30-day window to reduce complexity.