LeetCode 550: Game Play Analysis IV [SQL]

[@frederikmuller]

Updated solution to pass added test case: SELECT ROUND(COUNT(DISTINCT b.player_id)/COUNT(DISTINCT a.player_id),2) AS fraction FROM (SELECT player_id, MIN(event_date) AS event_date FROM Activity GROUP BY player_id) a LEFT JOIN Activity b ON a.player_id = b.player_id AND DATEDIFF(b.event_date, a.event_date) = 1 The only difference here is changing the left join date condition from a.event_date+1 = b.event_date to DATEDIFF(b.event_date, a.event_date) = 1, you could also use DATE_ADD(a.event_date, INTERVAL 1 DAY) = b.event_date. This is necessary as the new test case has Feb 29th as input which only occurs in a leap year. These special dates are only handled correctly by date manipulation functions such as DATE_ADD and DATEDIFF. What's to learn from this is that it's always best practice to use date manipulation functions instead of being lazy and using normal operations such a +1 and -1.

[@minimumeffort1997]

Hey. I was stuck with this problem for the whole day. I even read the solutions on leetcode. But none of them made sense to me in a logical way. Your video actually provides a step by step solution. Loved it. I hope I can learn to break down problems in a similar fashion someday.

[@frederikmuller]

awesome!

[@AbhishekPandey-c8o]

Actually this is the complicated solution i have even seen for this problem.

[@singhaaina94]

Your videos have been very helpful in understanding and writing logic. Please do keep posting more videos related to SQL problems!

[@frederikmuller]

I just finished recording some more and will upload them this week. I'm aiming for 3 videos a week!

[@prnvsgr]

Explaining each step and looking for output even if it is not what the question asked is so helpful !

[@frederikmuller]

Glad you like it!

[@GM-xz4xc]

Yes, i agree also... as someone who is self-taught , this step by step approach taught me how to approach such problems

[@hbkhan27]

A new test case is added on leetcode , on which your solution is failing , u need to update the code with date_add(a.event_date, interval 1 day)=b.event_date

[@frederikmuller]

Thank you for pointing this out!

[@Kornackifs]

4:55 but whyyyyy you want to see where you can't make that join you are already getting a correct answer

[@Hope-ig8ir]

Hello Fred, thanks for your videos. I wrote my code as: select count(distinct b.player_id) as umerator, count(distinct a.player_id) as denominator from Activity a left join Activity b on a.player_id = b.player_id and date(a.event_date) + interval 1 day = date(b.event_date) group by a.player_id but I don't know why I can't get the same result as yours. why can't I join like this first and then group by? Thanks

[@chargeurself760]

select round(count(distinct from b.player_id)/count(distinct a.player_id), 2) as fraction from (select player_id, min(event_date) as event_date from Activity group by player_id) a left join Activity b on a.player_id = b.player_id and date_add(a.event_date, interval 1 day) = b.event_date

[@MGtvMusic]

Keep making them Really helps

[@abdulrahmanmansy9607]

Thank you, your videos are so helpful.

[@MGtvMusic]

Thank you so much for these videos man

[@yimingwu8969]

what if this question asks for any two consecutive logins? Not just after the first login, how can we modify our code then?

[@frederikmuller]

just don’t take MIN(event_date) but any event_date

[@sfds1]

SELECT ROUND(COUNT(DISTINCT b.player_id) / COUNT(DISTINCT a.player_id), 2) AS fraction FROM ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) a LEFT JOIN Activity b ON a.player_id = b.player_id AND a.first_date + INTERVAL 1 DAY = b.event_date;

[@ishitvasingh9902]

Hey Frederik , first of all great job for posting these questions for us, these are definitely helping me in writing queries and understanding them more. Just wanted to ask you if this query will work for this question, with cte as ( select a.player_id from activity a join activity b on a.player_id=b.player_id where datediff(b.eventdate, a.eventadate) = 1 ) select round(( player_id/ (select count(distinct player_id) from activity)),2) as fraction from cte Will be looking forward for your reply Thanks

[@frederikmuller]

unfortunately, no, you have to change up the query but it's free to submit on LeetCode for this one

[@HenggaoCai]

You are on fire. Keep it up!

[@RishabSg]

What would happen if we self join the activity table to get the player id with consecutive days and then go about solving with cte

[@frederikmuller]

should work just as well as long as you divide the number of player_ids with consecutive logins by the number of all player_ids

[@GM-xz4xc]

Bro please continue to make such videos

[@frederikmuller]

just uploaded a few StrataScratch videos!

[@ayeshakousar4522]

Thank you so much for this series! They are awesome. By the way, I was wondering how can we do the same thing for 3 consecutive days? I tried adding an additional AND statement but didn't succeed :( can you please help?

[@frederikmuller]

you could change the join condition to LEFT JOIN Activity b ON a.player_id = b.player_id AND DATE_ADD(a.event_date, INTERVAL 3 DAY) = b.event_date, then add HAVING COUNT(DISTINCT b.event_date) = 3 after the GROUP BY. as you can see this makes the logic quite a bit more complex.

[@shubham900100]

Wouldn't this solution break if we had, let's say, one more row for player_id one which was consecutive. Count would give number 2 for player ID right?

[@frederikmuller]

No. First of all we use count distinct in our ratio calculation which wouldn’t count any player_id twice. Second of all there is only one first login date and there can only be one day following it so we wouldn’t get a match for 2 days after the first login date.

[@shubham900100]

@@frederikmuller Ohh right, I am stupid XD. Thanks for the explanation.

[@yuvrajmahendru2149]

Awesome! Very helpful..

[@mandardange2579]

Thank you so much !

[@inzamam483]

can we solve this question using lag function?

[@frederikmuller]

yes, but we would still have to check for the fact that the dates need to be exactly 1 day apart

[@vishwakirti512]

very helpful

[@tambeyash2498]

bro they have added 1 more test case to this and that test case in not getting passed by this solution Check it and reupload the video

[@frederikmuller]

I’ll take a look

[@frederikmuller]

I'm leaving a pinned comment on this video, it's just a small change to handle unexpected dates that occur in leap years.

[@Kornackifs]

why is this wrong for god sake #COUNT(*) AS consecutive_day_count SELECT ROUND(COUNT(DISTINCT t1.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity),2) AS fraction FROM Activity t1 JOIN Activity t2 ON DATEDIFF(t2.event_date, t1.event_date) = 1 AND t2.player_id = t1.player_id

[@frederikmuller]

Compare with the final code, you’re not using a LEFT JOIN, not using MIN() to get the first event_date for each player_id and you’re not selecting the DISTINCT COUNT from t1 and t2 The LEFT JOIN is used to make sure we include each player in the calculation, even if they don’t have a login on the following date.