1 题目描述

Table: Activity

+--------------+---------+  
| Column Name  | Type    |  
+--------------+---------+  
| player_id    | int     |  
| device_id    | int     |  
| event_date   | date    |  
| games_played | int     |  
+--------------+---------+  

(player_id, event_date) 是此表的主键 (具有唯一值的列的组合).
这张表显示了某些游戏的玩家的活动情况.
每一行是一个玩家的记录, 他在某一天使用某个设备注销之前登录并玩了很多游戏 (可能是 0).

2 测试用例

编写解决方案, 报告在首次登录的第二天再次登录的玩家的比率, 四舍五入到小数点后两位. 换句话说, 你需要计算从首次登录日期开始至少连续两天登录的玩家的数量, 然后除以玩家总数.

结果格式如下所示:

**示例 1: **
**输入: **

Activity table:  
+-----------+-----------+------------+--------------+  
| 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-02 | 0            |  
| 3         | 4         | 2018-07-03 | 5            |  
+-----------+-----------+------------+--------------+  

**输出: **

+-----------+  
| fraction  |  
+-----------+  
| 0.33      |  
+-----------+  

**解释: **
只有 ID 为 1 的玩家在第一天登录后才重新登录, 所以答案是 1/3 = 0.33

3 解题思路

1. 先按照 player_id 分组统计每个玩家第一次登录的时间 min(event_date) as loginDate
2. 左外链接 Activity 查找第一次登录后, 第二天有登录的玩家 left join Activity b on a.player_id = b.player_id and datediff(b.event_date, a.loginDate) = 1, 需要用到 datediff() 函数
3. 计算首次登录后连续两天都登录的玩家占所有玩家的比例, 需要用到 avg() 函数

3.1 解法 1:

解题思路的步骤 1

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

执行结果

player_idloginDate12016-03-0122017-06-2532016-03-02

解题思路的步骤 2

select a.*, b.*  
from (select player_id, min(event_date) as loginDate from Activity group by player_id) as a  left join Activity b on a.player_id = b.player_id and datediff(b.event_date, a.loginDate) = 1;  

执行结果

player_idloginDateplayer_iddevice_idevent_dategames_played12016-03-01122016-03-02622017-06-25nullnullnullnull32016-03-02nullnullnullnull

解题思路的步骤 3

select round(avg(b.event_date is not null), 2) as fraction  
from (select player_id, min(event_date) as loginDate from Activity group by player_id) as a  left join Activity b on a.player_id = b.player_id and datediff(b.event_date, a.loginDate) = 1;  

执行结果

fraction0.33