title: SQL count consecutive rows
body: <p>I have the following data in a table:</p>
  
  <pre><code>|event_id    |starttime        |person_id|attended|
  |------------|-----------------|---------|--------|
  | 11512997-1 | 01-SEP-16 08:00 | 10001   | N      |
  | 11512997-2 | 01-SEP-16 10:00 | 10001   | N      |
  | 11512997-3 | 01-SEP-16 12:00 | 10001   | N      |
  | 11512997-4 | 01-SEP-16 14:00 | 10001   | N      |
  | 11512997-5 | 01-SEP-16 16:00 | 10001   | N      |
  | 11512997-6 | 01-SEP-16 18:00 | 10001   | Y      |
  | 11512997-7 | 02-SEP-16 08:00 | 10001   | N      |
  | 11512997-1 | 01-SEP-16 08:00 | 10002   | N      |
  | 11512997-2 | 01-SEP-16 10:00 | 10002   | N      |
  | 11512997-3 | 01-SEP-16 12:00 | 10002   | N      |
  | 11512997-4 | 01-SEP-16 14:00 | 10002   | Y      |
  | 11512997-5 | 01-SEP-16 16:00 | 10002   | N      |
  | 11512997-6 | 01-SEP-16 18:00 | 10002   | Y      |
  | 11512997-7 | 02-SEP-16 08:00 | 10002   | Y      |
  </code></pre>
  
  <p>I want to produce the following results, where the maximum number of consecutive occurences where atended = 'N' is returned:</p>
  
  <pre><code>|person_id|consec_missed_max|
  | 1001    | 5               |
  | 1002    | 3               |
  </code></pre>
  
  <p>How could this be done in Oracle (or ANSI) SQL? Thanks!</p>
  
  <p>Edit:</p>
  
  <p>So far I have tried:</p>
  
  <pre><code>WITH t1 AS
  (SELECT t.person_id,
      row_number() over(PARTITION BY t.person_id ORDER BY t.starttime) AS idx
      FROM the_table t
      WHERE t.attended = 'N'),
  t2 AS
  (SELECT person_id, MAX(idx) max_idx FROM t1 GROUP BY person_id)
  SELECT t1.person_id, COUNT(1) ct
      FROM t1
      JOIN t2
      ON t1.person_id = t2.person_id
  GROUP BY t1.person_id;
  </code></pre>