KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
I have data in following format. 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 Here match_id s are in chronological order, 37 is the first and 217 is the last match played by team1. won_ind indicated whether the team won the match or not. 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. Team_id longest_streak ------------------------ Team1 4 Team2 3 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. I have created sample fiddle here .
Tags (comma-separated)
Save Edits
Cancel