Table: Activity+————–+———+ | Column Name | Type | +————–+———+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +————–+———+ (player_id,event_date)是此表的主键(具有唯一值的列的组合)。 这张表显示了某些游戏的玩家的活动情况。 每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。
编写解决方案,报告在首次登录的第二天再次登录的玩家的 比率,四舍五入到小数点后两位。换句话说,你需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
结果格式如下所示:
示例 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
解题思路
DATE_ADD判断日期
非常好的题,让我大脑旋转一小时。
这题写起来不难,有不过两个坑。第一个坑是题目要求的首次登录后连续登录两天的用户,所以需要MIN求某个用户的首次登录的日期。
第二个坑在于日期的处理。我们常用的时间判断有两种,最直接问题也最多的就是A.date+1=B.date。比较基础的就是datediff。碰到需要处理时间的题用datediff基本都能解决。
However,datediff有个坑。当datediff碰上2016-02-29和2016-03-01这两天时就会出现问题:datediff会返回NULL。
我个人认为是MySQL的datediff无法处理02-29 +1 = 03-01这种情况,对MySQL来说02-29+1=NULL,所以我们不能用datediff。
最后用的是DATE_ADD,DATE_ADD的用法如图,我个人还是推荐去MySQL文档里面找下相关用法的。

# Write your MySQL query statement below
SELECT ROUND(COUNT(IF(B.event_date IS NOT NULL,1,NULL))/COUNT(A.player_id),2) AS fraction
FROM Activity AS A
LEFT JOIN Activity AS B
ON A.player_id=B.player_id AND date_add(A.event_date,INTERVAL 1 DAY)=B.event_date
WHERE A.event_date=(
SELECT MIN(C.event_date)
FROM Activity AS C
WHERE A.player_id=C.player_id
GROUP BY C.player_id
)