SQL - 6

9.用户玩法分析

Activity表：显示了某些游戏的玩家的活动情况。

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
2	3	2017-06-25	1
3	1	2016-03-01	0
3	4	2018-07-03	5

需求一：写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。

展示效果：

player_id	first_login
1	2016-03-01
2	2017-06-25
3	2016-03-01

SQL：

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

需求二：描述每一个玩家首次登陆的设备名称

player_id	device_id
1	2
2	3
3	1

方法1：

select
	palyer_id,
	device_id
from 
	Activity
where 
	(player_id,event_date) in (select
                              	player_id,
                              	min(event_date)
                              from
                               	Activity
                              group by 
                               	player_id
                              );

方法2：

Select 
	player_id,
	device_id 
from 
	(select 
     	player_id,
     	event_date,
     	device_id
     	rank() over(partition by player_id order by event_date) rk
     from 
     	Activity
    ) t1
where rk = 1; 

需求三：编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。

player_id	event_date	games_played_so_far
1	2016-03-01	5
1	2016-05-02	11
2	2017-06-25	1
3	2016-03-02	0
3	2018-07-03	5

方法1：

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

方法2：

select
	player_id,
	event_date,
	sum(game_played) over(partition by player_id order by event_date) game_play_so_far
from 
	Activity;

需求四：编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的百分比，四舍五入到小数点后两位。

结果展示：

fraction

0.33

方法1：

select
  	round(
    	sum(
            case when datediff(a.event_date,b.event_date)=1 
            then 1 
            else 0 
            ebd 
        )/(select count(distinct player_id) from Activity)
    ,2 ) as fraction
from 
	Activity a,
	(select
     	player_id,
     	min(event_date) first_date
    from 
     	Activity b
    group by 
     	player_id
    )
where a.player_id = b.player_id 

方法2：

select
	round(avg(a.event_date is not null),2) 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 datediff(a.event_date,b.event_date) = 1    	

需求五：编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间。

install_dt	installs	Day1_retention
2016-03-01	2	0.50
2017-06-25	1	0.00

提示：玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天保留时间是 1/2=0.50
玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天保留为 0/1=0.00

SQL

select 
	a.event_date,
	count(a.player_id ) as install,
	round(count(c.player_id)/count(a.player_id),2) as Oneday_retention
from 
	Activity a
left join
	Activity b
on a.player_id = b.player_id and a.event_date > b.event_date
left join
	Activity c
on a.player_id = b.player_id and c.event_date = date_add(a.event_date,interval 1 day)
where 
	b.event_date is null
group by a.event_date

方法2

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