Flag Suspicious Card Transactions | Dataford Interview Questions

Your question is Flag Suspicious Card Transactions. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
risk_tier	VARCHAR(20)	Internal fraud risk tier

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
customer_id	INT	References customers.customer_id
account_status	VARCHAR(20)	Current account status
opened_at	DATE	Date the account was opened

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	References accounts.account_id
merchant_name	VARCHAR(100)	Merchant where the transaction occurred
transaction_ts	TIMESTAMP	Transaction timestamp
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status