1097.(Hard)游戏玩法分析 V

1097.(Hard)游戏玩法分析 V

题目描述

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         | 2016-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result 表：
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2        | 0.50           |
| 2017-06-25 | 1        | 0.00           |
+------------+----------+----------------+

玩家的 安装日期 定义为该玩家的第一个登录日。
玩家的 第一天留存率 定义为：假定安装日期为 X 的玩家的数量为 N ，其中在 X 之后的某一天重新登录的玩家数量为 M ，M/N 就是第一天留存率，四舍五入到小数点后两位。
编写一个 SQL 查询，报告所有安装日期、当天安装游戏的玩家数量和玩家的第一天留存率。

题目分析

留存问题，先计算出每个用户的初始登陆日期，接着将此日符合要求的用户同初始唯独没有下产生的用户做比较。

代码实现

select
    min_date
    ,count(distinct player_id) user_cnt
    ,round(count(distinct if(datediff(event_date,min_date)=1,player_id,null))/count(distinct player_id),2) day1_ratio
from 
    Activity t1 
join 
    (
    select
        player_id
        ,device_id
        ,min(event_date) min_date
    from 
        Activity
    group by 
        player_id
        ,device_id
    ) t2 
on t1.player_id=t2.player_id and t1.device_id=t2.device_id
group by 
    min_date
;

从一名不羁的码农开始，谈风月之余谈技术