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
作者:yuexiuping
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
posted @ 2021-06-17 22:29  yuexiuping  阅读(15)  评论(0编辑  收藏  举报