title: T-SQL: Count number of failures until first success
body: <p>I have a DB consisting of events with timestamps:</p>
  
  <pre><code>row eventName taskName timestamp userName
  1   fail      ABC      10.5      John
  2   fail      ABC      18.0      John
  3   fail      ABC      19.0      Mike
  4   fail      XYZ      21.0      John
  5   fail      XYZ      23.0      Mike
  6   success   ABC      25.0      John
  7   fail      ABC      26.0      John
  8   success   ABC      28.0      John
  </code></pre>
  
  <p>I'd like to count the number of failures until the first success, per user (and average, but that's beyond this question).</p>
  
  <p>In the example above, John attempted task ABC 2 times (rows 1 &amp; 2) until succeeding (row 6). Subsequent failures and successes can be ignored.</p>
  
  <p>I think I could achieve this by counting the number of rows with "ABC" and "fail" whose timestamp is earlier than the earliest timestamp in all rows with "ABC" and "success", grouping by userName. How do I express this in T-SQL? Specifically, Vertica.</p>
  
  <p>This seemed to be very similar to the case here:
  <a href="https://stackoverflow.com/questions/39551595/sql-count-sum-the-number-of-calls-until-a-specific-date-in-another-column">sql count/sum the number of calls until a specific date in another column</a></p>
  
  <p>But when I tried to adapt the code from <a href="https://stackoverflow.com/a/39594686/4354459">https://stackoverflow.com/a/39594686/4354459</a> as below, I think I got something wrong because I keep getting larger counts than expected.</p>
  
  <pre><code>WITH
  Successes
  AS
  (
      SELECT
          events.userName
          ,events.taskName
          ,MIN(events.timestamp) AS FirstSuccessTime
      FROM events
      WHERE events.eventName = 'success'
      GROUP BY events.userName, events.taskName
  )
  SELECT
      events.userName
      ,events.taskName
      ,COUNT(events.eventName) AS FailuresUntilFirstSuccess
  FROM
      Successes
      LEFT JOIN events
          ON  events.taskName = Successes.taskName
          AND events.timestamp &lt; Successes.FirstSuccessTime
          AND events.eventName = 'fail'
  GROUP BY events.userName, events.taskName
  ;
  </code></pre>