Rolling Five-Minute Transaction Threshold | Dataford Interview Questions

Your question is Rolling Five-Minute Transaction Threshold. Start with the requirements and the one table 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

NICE Actimize monitors transaction activity for rapid bursts that may indicate suspicious behavior. Write a PostgreSQL query to identify users who exceed three transactions in any inclusive rolling five-minute window.

Requirements

Partition activity by user_id and order it by transaction_ts.
Count transactions from the current timestamp through the preceding five minutes, including both boundaries.
Return only users whose maximum window count is greater than three.
Include the earliest timestamp where the threshold was exceeded and the maximum transaction count observed.
Ignore rows with a missing user_id or transaction_ts, and sort results by user_id.

Schema

customer_transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
user_id	BIGINT	Customer or account identifier
transaction_ts	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is Rolling Five-Minute Transaction Threshold. Start with the requirements and the one table 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.

Requirements

Partition activity by user_id and order it by transaction_ts.
Count transactions from the current timestamp through the preceding five minutes, including both boundaries.
Return only users whose maximum window count is greater than three.
Include the earliest timestamp where the threshold was exceeded and the maximum transaction count observed.
Ignore rows with a missing user_id or transaction_ts, and sort results by user_id.

Schema

customer_transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
user_id	BIGINT	Customer or account identifier
transaction_ts	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount