Hey Zach just watched your most recent video and noticed an issue with how you identify streaks. The window function in the streak_identified CTE calculates only the total count of times the player's streak changed in the playlist, rather than assigning a unique identifier to each streak. For example the player "My Regret" has a streak_identifier that seems to stay the same across several different win/loss streaks.
I think you can add a window frame rows between unbounded preceding and current row to your window here and get the desired behavior, where different streaks have their own unique streak_identifier (unique to the player/playlist).
Hey Zach just watched your most recent video and noticed an issue with how you identify streaks. The window function in the streak_identified CTE calculates only the total count of times the player's streak changed in the playlist, rather than assigning a unique identifier to each streak. For example the player "My Regret" has a streak_identifier that seems to stay the same across several different win/loss streaks.
I think you can add a window frame
rows between unbounded preceding and current row
to your window here and get the desired behavior, where different streaks have their own unique streak_identifier (unique to the player/playlist).https://github.com/EcZachly/video-game-training-sql/blob/master/window-function-training/rank_filter_streaks.sql#L27