The security team wants to flag accounts that may be under attack. Using the login_attempts table, find every user with more than 3 failed login attempts in the last 30 days — return user_id and failed_count, sorted by failed_count descending.
For this problem, treat "the last 30 days" as attempts where attempted_at >= NOW() - INTERVAL '30 days'. The test cases supply explicit timestamps relative to a fixed reference point, so the grader will run your query against data where all relevant rows already fall within the window.
login_attempts
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| success | BOOLEAN |
| attempted_at | TIMESTAMP |
| id | user_id | success | attempted_at |
|---|---|---|---|
| 1 | 1 | false | 2024-04-10 08:00:00 |
| 2 | 1 | false | 2024-04-11 09:00:00 |
| 3 | 1 | true | 2024-04-12 10:00:00 |
| 4 | 1 | false | 2024-04-13 11:00:00 |
| 5 | 1 | false | 2024-04-14 12:00:00 |
| 6 | 2 | false | 2024-04-10 08:00:00 |
| 7 | 2 | false | 2024-04-11 09:00:00 |
| 8 | 3 | false | 2024-04-10 08:00:00 |
| user_id | failed_count |
|---|---|
| 1 | 4 |
User 1 has 4 failed attempts (rows 1, 2, 4, 5) — above the threshold of 3. User 2 has 2 failed attempts and user 3 has 1 — both are excluded. The successful attempt for user 1 (row 3) does not count.