search

EcZachly / video-game-training-sql

Hey this is the repo that has all the queries and data for my video game training series!
126 stars 23 forks source link

Possible bug in streak identification logic #3

Open randyharnarinesingh opened 2 years ago

randyharnarinesingh commented 2 years ago

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.

image

randyharnarinesingh commented 2 years ago

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 commented 2 years ago

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

randyharnarinesingh commented 2 years ago

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