Possible bug in streak identification logic

[randyharnarinesingh]

Hi Zach, thanks much for making this tutorial. It was very useful to me!

However, I wanted to point out some strange behavior in the streak identification logic for your tutorial. I have attached a screenshot of the relevant columns obtained from the lengths table. The completion_date column does not seem to give any hour/minute/second granularity for matches completed on the same day. Therefore, the window function (in the grouped table) that is ORDERED BY completion_date would return the same result for each row in every distinct player_gamertag/playlist group that happens to take place on the same day. You can see in the attached screenshot how the downstream logic in subsequent CTEs indicates a streak of 17 for this player even though he/she had actual breaks in their streak on the given day.

[randyharnarinesingh]

Actually, I think unless we had the hour/minute/second granularity for matches, we can't actually identify chronological streaks since we are left without the actual match sequence for matches that occur on the same day

[EcZachly]

@randyharnarinesingh you're totally right. I need to update the ETLs for this to preserve the hours, minutes, and seconds.

[randyharnarinesingh]

got it! I was assuming the original data just didn't have H/M/S levels but now thinking about, that sounds a bit silly lol...thanks for the response