Write a query to detect accounts with more than 5 failed login attempts within a 10-minute window.

Answer

Use a self-join to pair each failed login with other failed logins from the same account within 10 minutes, then flag accounts with 5 or more in that window.

Solution

SELECT DISTINCT a.account_id
FROM LoginAttempts a
JOIN LoginAttempts b
  ON a.account_id = b.account_id
  AND b.attempt_time BETWEEN a.attempt_time AND a.attempt_time + INTERVAL '10 minutes'
  AND a.status = 'failed' AND b.status = 'failed'
GROUP BY a.account_id, a.attempt_time
HAVING COUNT(*) >= 5;

Step by Step

1. The self-join pairs every failed attempt (a) with every other failed attempt (b) from the same account occurring within the next 10 minutes.
2. Grouping by account_id, attempt_time (using each a row as a rolling window start) and counting matches tells you how many failed attempts cluster around that starting point.
3. HAVING COUNT(*) >= 5 flags any 10-minute window containing 5 or more failed attempts.

Alternative: Window Function Approach (Simpler, More Scalable)

WITH failed AS (
  SELECT account_id, attempt_time,
         COUNT(*) OVER (
           PARTITION BY account_id
           ORDER BY attempt_time
           RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW
         ) AS attempts_in_window
  FROM LoginAttempts
  WHERE status = 'failed'
)
SELECT DISTINCT account_id FROM failed WHERE attempts_in_window >= 5;

A RANGE-based window function directly counts failed attempts within a trailing 10-minute range per account, without the self-join's combinatorial row explosion - meaningfully more efficient on large login logs.

Key Exam Traps

• A self-join on a time range creates a row for every pair within the window, which can be very expensive on large datasets - flagging the window-function alternative as the production-scale answer is important here.
• "Within a 10-minute window" is a sliding window, not fixed 10-minute buckets - 5 failed attempts spanning from 9:58 to 10:07 should count, even though they cross a fixed clock-hour boundary; a naive GROUP BY on truncated 10-minute buckets would miss exactly this kind of case.
• Successful login resets nothing in this query - if the real security requirement is "5 failed attempts without an intervening success," that's a materially different (and more complex) condition than this time-window-only check.