Write a SQL query to find the top 5 user accounts with the highest number of failed payment attempts within a rolling 10-minute window at Affirm.
Use the provided account and payment-attempt data. Consider only failed attempts. Return one row per account, selecting the latest window when an account has multiple windows with the same maximum count.
user_account_id, max_failed_attempts_10m, window_start, and window_endmax_failed_attempts_10m descending, then user_account_id ascending| Column | Type | Description |
|---|---|---|
| user_account_idPK | INT | Unique Affirm user account identifier |
| account_type | VARCHAR(30) | Account classification |
| Column | Type | Description |
|---|---|---|
| payment_attempt_idPK | INT | Unique payment attempt identifier |
| user_account_id | INT | Account associated with the payment attempt |
| attempted_at | TIMESTAMP | Timestamp when the payment attempt occurred |
| is_failed | BOOLEAN | Whether the payment attempt failed |
| failure_reason | VARCHAR(40) | Optional reason for a failed attempt |