LeetCode 511, 512, 534, 550 Game Play Analysis

(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

drop table Activity
 
Create table Activity(player_id int,device_id int,event_date date,games_played int);
 
insert into Activity values(1,2,'2016-03-01',5);
insert into Activity values(1,2,'2016-05-02',6);
insert into Activity values(2,3,'2017-06-25',1);
insert into Activity values(3,1,'2016-03-02',0);
insert into Activity values(3,4,'2018-07-03',5);

511.Game Play Analysis I

Write an SQL query that reports the first login date for each player.

select player_id,min(event_date) as first_login 
from Activity
group by player_id

512.Game Play Analysis II

Write a SQL query that reports the device that is first logged in for each player.

with cte as
(
select player_id,min(event_date) as first_Login
from Activity
group by player_id
) select a.player_id,a.device_id
from Activity a join cte c
on a.player_id=c.player_id
and a.event_date=c.first_Login

select a.player_id,a.device_id
from Activity a
right join
(
select player_id,min(event_date) as first_login
from Activity
group by player_id
) b
on a.player_id=b.player_id
and a.event_date=b.first_login 

534. Game Play Analysis III

Write an SQL query that reports for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date. Check the example for clarity.
 

drop table Activity
 
Create table Activity(player_id int,device_id int,event_date date,games_played int);
 
insert into Activity values(1,2,'2016-03-01',5);
insert into Activity values(1,2,'2016-05-02',6);
insert into Activity values(1,3,'2017-06-25',1);
insert into Activity values(3,1,'2016-03-02',0);
insert into Activity values(3,4,'2018-07-03',5);

select a.player_id,a.event_date,sum(b.games_played) as games_played_so_far
from Activity a left join
Activity b
on a.player_id=b.player_id
and a.event_date>=b.event_date
group by a.player_id,a.event_date
order by a.player_id,a.event_date asc

Windows Function

select player_id,event_date,
sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity

550. Game Play Analysis IV

Write an SQL query that reports the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
 

drop table Activity
 
Create table Activity(player_id int,device_id int,event_date date,games_played int);
 
insert into Activity values(1,2,'2016-03-01',5);
insert into Activity values(1,2,'2016-03-02',6);
insert into Activity values(2,3,'2017-06-25',1);
insert into Activity values(3,1,'2016-03-02',0);
insert into Activity values(3,4,'2018-07-03',5);

select * from Activity

CTE solutions:

with cte as(
select player_id,min(event_date) as first_login
from Activity
group by player_id
) 
,cte2 as
(
select c.player_id
from cte c
join Activity a
on a.player_id = c.player_id
and DATEDIFF(DAY,c.first_login,a.event_date) = 1
) select round(cast(count(distinct(c2.player_id)) as float)/cast(count(distinct(a.player_id))as float),2) as fraction
from Activity a
left join cte2 c2 on a.player_id=c2.player_id