大数据第41天—Mysql练习题12-游戏玩法分析-杨大伟

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

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

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

展示效果：

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

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);

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

最终SQL:

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

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

player_id	device_id
1	2
2	3
3	1

最终SQL:

select 
      player_id,
      device_id 
from
     (select *  
      from 
          Activity
      where
          (player_id,event_date) in (select
                                           player_id, 
                                           device_id
                                           min(event_date)
                                      from
                                           Activity 
                                      group by 
                                           player_id
                                      )
      ) as t;

需求三：编写一个 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

提示：对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。

最终SQL:

//方法一
SELECT C.player_id,C.event_date,C.games_played_so_far
FROM (
      SELECT 
          A.player_id,
          A.event_date,
          @sum_cnt:=
              if(A.player_id = @pre_id AND A.event_date != @pre_date,
                  @sum_cnt + A.games_played,
                  A.games_played 
              )
              AS `games_played_so_far`,
          @pre_id:=A.player_id AS `player_ids`,
          @pre_date:=A.event_date AS `event_dates`
      FROM 
          activity AS A,
          (SELECT @pre_id:=NULL,@pre_date:=NULL,@sum_cnt:=0) AS B
      order BY 
          A.player_id,A.event_date
) AS C

//方法二
SELECT 
      B.player_id,
      B.event_date,
      SUM(A.games_played) AS `games_played_so_far`
FROM 
      Activity AS A
JOIN 
      Activity AS B 
ON 
      A.player_id = B.player_id 
      AND A.event_date <= B.event_date
GROUP BY 
      B.player_id,B.event_date;

需求四：编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的百分比，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

fraction

0.00

提示：对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏. 对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。

最终SQL:

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

需求五：编写一个 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.install_date,
      COUNT(A.player_id) AS `installs`,
      COUNT(AA.player_id) AS `return_cnt`
FROM 
   (SELECT 
           player_id,
           MIN(event_date) AS `install_date`
    FROM 
           Activity
    GROUP BY 
           player_id
    ) AS A
left JOIN 
    Activity AS AA 
ON 
    AA.event_date = DATE_ADD(A.install_date,INTERVAL 1 DAY) AND AA.player_id = A.player_id
GROUP BY
    A.install_date;

#方法二
SELECT 
      A.event_date AS `install_dt`,
      COUNT(A.player_id) AS `installs`,
      round(COUNT(C.player_id)/COUNT(A.player_id),2) AS `Day1_retention`
FROM
      Activity AS A 
left JOIN 
      Activity AS B
ON 
      A.player_id = B.player_id AND A.event_date > B.event_date
left JOIN 
      Activity AS C
ON    
      A.player_id = C.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;