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 + 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, not rows. Failure rate = failed logical attempts / total logical attempts.
4. 4Sanity-check the denominator. Confirm attempt count is below row count by roughly your retry rate; if they're equal, your dedup didn't fire.

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;