LeetCode 550: Game Play Analysis IV [SQL]

Solution and walkthrough of leetcode database problem 550: Game Play Analysis IV. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.
Link to the problem: leetcode.com/p...
Game Play Analysis I: • LeetCode 511: Game Pla...
Game Play Analysis II: • LeetCode 512: Game Pla...
Game Play Analysis III: • LeetCode 534: Game Pla...
Game Play Analysis IV: • LeetCode 550: Game Pla...
Playlists:
LeetCode database SQL problems: • LeetCode Database Prob...
Easy SQL problems: • LeetCode Easy Database...
Medium SQL problems: • LeetCode Medium Databa...
Hard SQL problems: • LeetCode Hard Database...

[@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.

[@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!

[@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!

[@user-tw3rf6oo9h]

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

[@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!

[@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

[@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

[@GM-xz4xc]

Bro please continue to make such videos

[@frederikmuller]

just uploaded a few StrataScratch videos!

[@abdulrahmanmansy9607]

Thank you, your videos are so helpful.

[@HenggaoCai]

You are on fire. Keep it up!

[@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

[@mrstealyourkill]

May I know why this code is not working? select round((count(a.test)/count(distinct a.player_id)),2) fraction from (select player_id, case when event_date + 1 = lead(event_date,1) over (partition by player_id order by event_date) then 1 else null end as test from activity)a; It passes the primary test cases but not the others

[@frederikmuller]

look at the pinned comment, leetcode added a test case with data containing a leap year (like this year). you’ll need to change the event_date + 1 to a proper date addition function.

[@MGtvMusic]

Keep making them Really helps

[@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

[@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

[@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;

[@mandardange2579]

Thank you so much !

[@yuvrajmahendru2149]

Awesome! Very helpful..

[@Kornackifs]

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

[@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.

[@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

[@vishwakirti512]

very helpful

[@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.

[@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

[@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.