SQL: Failed Attempts Then Success | Dataford Interview Questions

Write a SQL query to identify users who have had more than three failed transaction attempts followed by a successful one within a 10-minute window at Sift.

Use the users and transaction_attempts tables. Return each qualifying user once, using their earliest qualifying successful transaction.

Output

One row per qualifying user, with user_id, successful_attempt_id, successful_at, and failed_attempt_count
Include only users with more than three failed attempts in the preceding ten minutes
Order by user_id ascending, then successful_attempt_id ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Sift user identifier
email	VARCHAR(255)	User email address

transaction_attempts

Column	Type	Description
attempt_idPK	INT	Unique transaction attempt identifier
user_id	INT	User associated with the attempt
status	VARCHAR(20)	Attempt outcome, such as failed or success
attempted_at	TIMESTAMPTZ	Timestamp when the attempt occurred

Your question is SQL: Failed Attempts Then Success. Start with the requirements and the two tables in the Question tab.

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

Problem