Task 3: dedup retries into logical attempts before a failure rate

Task 3: dedup retries into logical attempts before a failure rate

A flaky network produces three rows for one user intent: two timeouts then an ok. Counting raw rows reports a 67% failure rate for something the user experienced as a success. Roll up to the logical attempt first.

1. 1Define the logical attempt. Group retries under one key: request_id, or a window of user_id plus the same prompt within N seconds if no request_id exists.
2. 2Collapse status with a precedence rule. A logical attempt succeeds if any of its tries succeeded; it fails only if every try failed. Decide and state the rule.
3. 3Compute the rate on attempts. Failure rate = failed logical attempts / total logical attempts.
4. 4Sanity-check the denominator. Same check as the gate in the run above.

WITH attempt AS (
  SELECT
    request_id,
    BOOL_OR(status = 'ok')                              AS any_ok,
    COUNT(*)                                            AS tries
  FROM interaction_events
  WHERE event_type IN ('success', 'error', 'timeout')
  GROUP BY request_id
)
SELECT
  COUNT(*)                                              AS logical_attempts,
  COUNT(*) FILTER (WHERE NOT any_ok)                     AS failed_attempts,
  ROUND(100.0 * COUNT(*) FILTER (WHERE NOT any_ok)
              / COUNT(*), 2)                             AS failure_rate_pct,
  ROUND(AVG(tries), 2)                                  AS avg_tries
FROM attempt;