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

1. Consider only transactions where status = 'declined'.
2. Group suspicious activity by account_id and calendar date.
3. Return accounts where a 1-hour window contains at least 3 declined transactions.
4. Output: account_id, customer_name, transaction_date, first_decline_time, last_decline_time, and declined_count.
5. Order results by transaction_date ascending, then declined_count descending, then account_id.

Table Definitions

customers

accounts

transactions

Sample Data

Representative rows are included below in the dataset.

Expected Output