title: Finding the longest streak of wins
body: <p>I have data in following format.</p>
  
  <pre><code>match_id   team_id   won_ind
  ----------------------------
  37          Team1    N
  67          Team1    Y
  98          Team1    N
  109         Team1    N
  158         Team1    Y
  162         Team1    Y
  177         Team1    Y
  188         Team1    Y
  198         Team1    N
  207         Team1    Y
  217         Team1    Y
  10          Team2    N
  13          Team2    N
  24          Team2    N
  39          Team2    Y
  40          Team2    Y
  51          Team2    Y
  64          Team2    N
  79          Team2    N
  86          Team2    N
  91          Team2    Y
  101         Team2    N
  </code></pre>
  
  <p>Here <code>match_id</code>s are in chronological order, 37 is the first and 217 is the last match played by team1. <code>won_ind</code> indicated whether the team won the match or not. </p>
  
  <p>So, from the above data, team1 has lost its first match, then won a match, then lost 2 matches, then won 4 consecutive matches and so on. Now I'm interested in finding the longest winning streak for each team.</p>
  
  <pre><code>Team_id   longest_streak
  ------------------------
  Team1     4
  Team2     3
  </code></pre>
  
  <p>I know how to find this in plsql, but i was wondering if this can be calculated in pure SQL. I tried using LEAD, LAG and several other functions, but not getting anywhere. </p>
  
  <p>I have created sample fiddle <a href="http://sqlfiddle.com/#!4/31f95/1">here</a>.</p>