Task
NovaPay wants to flag accounts with potentially fraudulent card activity. Write a SQL query to identify accounts that had at least 3 declined transactions within any 1-hour window on the same day.
Return only suspicious accounts and include the time window summary.
Requirements
- Consider only transactions where
status = 'declined'.
- Group suspicious activity by
account_id and calendar date.
- Return accounts where a 1-hour window contains at least 3 declined transactions.
- Output:
account_id, customer_name, transaction_date, first_decline_time, last_decline_time, and declined_count.
- Order results by
transaction_date ascending, then declined_count descending, then account_id.
Table Definitions
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| risk_tier | VARCHAR(20) | Internal risk segment |
| | |
accounts
| column | type | description |
|---|
| account_id | INT | Unique account identifier |
| customer_id | INT | References customer |
| account_status | VARCHAR(20) | Current account status |
| opened_at | DATE | Account open date |
| | |
transactions
| column | type | description |
|---|
| transaction_id | INT | Unique transaction identifier |
| account_id | INT | References account |
| merchant_name | VARCHAR(100) | Merchant name |
| transaction_ts | TIMESTAMP | Transaction timestamp |
| amount | DECIMAL(10,2) | Transaction amount |
| status | VARCHAR(20) | approved, declined, or pending |
Sample Data
Representative rows are included below in the dataset.
Expected Output
| account_id | customer_name | transaction_date | first_decline_time | last_decline_time | declined_count |
|---|
| 101 | Alice Chen | 2024-06-01 | 2024-06-01 09:05:00 | 2024-06-01 09:50:00 | 3 |
| 104 | Diana Park | 2024-06-02 | 2024-06-02 14:00:00 | 2024-06-02 14:40:00 | 3 |
