GSN GAMES LeetCode Medium “Game Play Analysis IV" 550 Interview SQL Question Explanation | EDS

[@LOVE-kf9xi]

Thanks for this simple explanation, I was struggling on this question for more than two hours , I even came up with my own solution - With first_login AS ( SELECT player_id, MIN(event_date) as firstlogin FROM Activity GROUP BY player_id), how_many AS ( SELECT Activity.player_id, SUM(CASE WHEN DATEDIFF(event_date, firstlogin) = 1 THEN 1 ELSE 0 END) as istrue FROM Activity LEFT JOIN first_login ON Activity.player_id = first_login.player_id GROUP BY player_id HAVING istrue = '1' ) SELECT ROUND( (SELECT COUNT(*) FROM how_many) / (SELECT COUNT(DISTINCT(player_id)) FROM Activity) , 2) as fraction

[@neerajkumawat3386]

with cte as ( select *, min(event_date) over(partition by player_id) as first_login from activity ) select round(count(distinct a1.player_id) / (select count(distinct player_id) from activity), 2) as fraction from cte a1 join cte a2 on a1.player_id = a2.player_id and datediff(a2.event_date, a1.event_date) = 1 and a1.first_login = a1.event_date

[@prachikatayade676]

with cte as ( select player_id ,event_date, min(event_date) over(partition by player_id) as first_login from activity ) select round(sum(decode(event_date-first_login,1,1,0)) /count(distinct player_id),2) fraction from cte

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@Karthikmahi007]

Hi Sir, Big Big fan of your channel. I have written one query for this question which is a bit too long, I don't know where it is missing , but it's not passing all test cases. WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date ASC) AS rk FROM activity ), cte2 AS ( SELECT player_id, event_date, rk, DATE_SUB(event_date, INTERVAL rk DAY) AS grp FROM cte ), cte3 AS ( SELECT *, LEAD(grp, 1) OVER (PARTITION BY player_id ORDER BY event_date ASC) AS next_grp FROM cte2 ), consecutive_players AS ( -- This CTE counts players who logged in on consecutive days SELECT player_id FROM cte3 WHERE grp = next_grp GROUP BY player_id ), total_players AS ( -- Count total distinct players SELECT COUNT(DISTINCT player_id) AS total FROM activity ) -- Now, calculate the ratio of consecutive login players to total players SELECT ROUND(COUNT(DISTINCT consecutive_players.player_id) / total_players.total, 2) AS fraction FROM consecutive_players, total_players; Can anyone pls debug and let me know the correct fix?

[@this_is_me-t5u]

It took me hours to solve this question, here's my solution: WITH cte as( SELECT *, MIN(event_date) OVER (PARTITION BY player_id) AS first_login_date, RANK() OVER (PARTITION BY player_id ORDER BY event_date) as rn FROM Activity ), cte2 as( SELECT COUNT(DISTINCT player_id) AS cnt FROM cte WHERE DATEDIFF(event_date, first_login_date)+1 = rn GROUP BY player_id having count(*)>=2 ) SELECT IFNULL(ROUND(SUM(cnt)/(SELECT COUNT(DISTINCT player_id) FROM Activity),2), 0.00) AS fraction FROM cte2;

[@vbmudalige]

with cte as ( select *, first_value(event_date) over( partition by player_id order by event_date ) as first_date, lead(event_date) over( partition by player_id order by event_date ) as next_date from Activity ) select round(count(distinct player_id)/(select count(distinct player_id) from Activity), 2) as fraction from cte where next_date = date_add(first_date, interval 1 day)

[@tejaswaniguttula5961]

# Write your MySQL query statement below with cte1 as (select *, row_number() over(partition by player_id order by event_date) as rn from activity), cte2 as (select * from cte1 where rn = 1) select round((select count(distinct cte2.player_id) from cte2 inner join cte1 on cte2.event_date = date_sub(cte1.event_date , interval 1 day) where cte2.player_id = cte1.player_id)/ ( select count(distinct player_id) from activity ), 2) as fraction;

[@gontlakowshik2345]

Hi Bro, Please take some time and add the sql schema for your old videos in the comment section or description box it will be helpful for us to practice the questions. Thankyou.

[@EverydayDataScience]

Sure, I’ll try to slowly add them, it’s a lot of videos so might take some time.

[@ariefahmedshaik729]

can we do this ? select round(count(*) /(select count(distinct player_id ) from Activity ) ,2) fraction from Activity b where b.player_id in (select a.player_id from Activity a where a.player_id = b.player_id and a.event_date = b.event_date -1)

[@shravank1147]

explained really well.. one question is there any platform better than leetcode and stratasractch for sql practicce ?

[@EverydayDataScience]

I am not sure if it’s better because I haven’t solved anything from it but DataLemur is another platform where you can practice SQL questions. I think Leetcode, Stratascratch and DataLemur combined should be enough for at least Medium level understanding of SQL.

[@shravank1147]

@@EverydayDataScience sure thankyou

[@PradeepMunirathnam]

WITH CTE AS( SELECT *, LEAD(event_date) OVER(PARTITION BY player_id ORDER BY event_date) as next_date FROM Activity2 ), CTE1 AS( SELECT COUNT(*) as loggin_count FROM CTE WHERE (COALESCE(next_date, date('1999-01-01')) - event_date) = 1 ) SELECT ROUND( (SELECT loggin_count FROM CTE1) * 1.0 / -- Doing this 1.0 multiplication to make one of the integer to decimal. Else the result will be 0.00 due to integer getting divided by integer (SELECT COUNT(DISTINCT(player_id)) FROM Activity2), 2 ) As fraction

[@nagendrabommireddi8437]

have a doubt iam begginner . why cant we write derive that next day in the same cte (1st cte itself?) please answer

[@reachrishav]

Thanks a lot again 👍

[@rupdeepthey]

Thanks for making these.

[@EverydayDataScience]

Glad that you found the video useful 😊

[@lokeshbisen4707]

are you able to please review my below query? it runs successfully but while submitting gives wrong answer with cte as ( select *, lead(event_date) over(partition by player_id order by event_date) as next_date from Activity) select round(sum(case when DATEDIFF(next_date,event_date) = 1 then 1 else 0 end)/count(distinct player_id),2) as fraction from cte;

[@AakifKhan-h9e]

I did the same found why it is failing??

[@williamlu647]

Is this correct? WITH CTE AS (SELECT *, DATE_SUB(event_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY player_id ORDER BY event_date) DAY) AS diff FROM Activity) SELECT COUNT(T.player_id)/ (SELECT COUNT(DISTINCT player_id) FROM Activity) AS tt FROM ( SELECT player_id, diff, COUNT(*) AS cnt FROM CTE GROUP BY player_id, diff HAVING COUNT(*)>1) T

[@srikrishnan8097]

Hey man you have explained the problem nicely and thanks for that. Can you help me why my solution is not passing all test cases even though it seems it is also doing the same " WITH cte AS (SELECT COUNT(DISTINCT a1.player_id) AS cnt FROM Activity a1 INNER JOIN Activity a2 ON a1.player_id=a2.player_id AND DATE_ADD(a1.event_date, INTERVAL 1 DAY)=a2.event_date) SELECT ROUND(cnt/(SELECT COUNT(DISTINCT player_id) FROM Activity),2) AS fraction FROM cte"

[@nabinagoswami1261]

WITH Final As( WITH CTE AS( SELECT player_id , event_date as current_event_date ,lead(event_date) Over(PARTITION BY player_id order by event_date) as Next_event_date FROM `leetcode-questions.LEETCODE.Activity` ORDER BY 1) SELECT count(*) FROM (SELECT DISTINCT player_id , IFNULL(DATE_DIFF(Next_event_date,current_event_date,DAY),0) AS Date_Difference FROM CTE WHERE IFNULL(DATE_DIFF(Next_event_date,current_event_date,DAY),0) =1) AS Total_Count_Of_Players_Who_Logged_Consecutive_Days) SELECT ROUND((SELECT count(*) From Final) / (Select count(DISTINCT player_id) FROM `leetcode-questions.LEETCODE.Activity`),2) AS RESULT

[@NitishKumar-l5s9s]

Can you tell me why below query is not passing all the test cases?? SELECT ROUND(COUNT(a.player_id)/(SELECT(COUNT(DISTINCT player_id))FROM activity a),2) AS fraction FROM activity a JOIN activity b ON a.player_id = b.player_id AND a.event_date = (SELECT DATE_ADD(b.event_date, INTERVAL 1 DAY))

[@suba_sah]

@@NitishKumar-l5s9s First I thought it would work too.

[@Kingabs732]

bro your solution is 2 complicated, you yourself says not to use sub-queries but in this video, you crossed all the limit. So many sub-queries haha my solution, although its not perfect. but not complicated and accepted with all the test cases. With cte as ( Select *, min(event_date) over(partition by player_id order by event_date) as Ist_Date, LAG(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS lag_date, LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS lead_date From Activity) Select ROUND(Count(DISTINCT player_id)/ (select count(distinct player_id) from cte),2) AS fraction FROM cte WHERE DATEDIFF(lead_date, Ist_Date) < 2;

[@Moath268]

why this not pass all cases any one know ? with RankedActivity As( Select Activity.player_id,Activity.event_date,Activity.device_id,Activity.games_played, LEAD(Activity.event_date,1) OVER (ORDER BY Activity.player_id) AS NextDay, LEAD(Activity.player_id,1) OVER (ORDER BY Activity.player_id) AS IDplayerNextDay from Activity ) Select ROUND( Cast(Count(*) AS FLOAT) / CAST( (SELECT COUNT(DISTINCT Activity.player_id) FROM Activity) AS FLOAT ),2) AS fraction from RankedActivity where RankedActivity.player_id = RankedActivity.IDplayerNextDay AND DATEDIFF(DAY ,RankedActivity.event_date,RankedActivity.NextDay) = 1;