【LC中等】550. 游戏玩法分析 IV

❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈，孜孜不倦

1.题目描述

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）

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

查询结果格式如下所示：

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            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

2.代码实现

此题的重点在于首次登陆，如果不要求首次登陆，用如下代码就可以实现

SELECT ROUND( count( DISTINCT t1.player_id )
             /( SELECT count( DISTINCT player_id ) FROM Activity ),2 ) AS fraction 
FROM
	Activity t1 JOIN Activity t2 ON t1.player_id = t2.player_id 
	AND t1.event_date <> t2.event_date 
WHERE
	DATEDIFF( t2.event_date, t1.event_date ) = 1

因为要首次登陆，因此我们可以用多列in的方法，筛选出第一次登陆时间后一天的数据与原表进行匹配

SELECT
	ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(distinct player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id,event_date) IN
	(SELECT 
        player_id,
        Date(min(event_date)+1)
	FROM Activity
	GROUP BY player_id);

其实这样会有一个bug，如果首次登陆日期是某月最后一天，+1之后再套用date函数会返回null值。

举例：Date(min(20200331)+1) 返回值为null而不是20200401。此处最好使用DATE_ADD函数替代。

SELECT
	ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(distinct player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id,event_date)
	IN
	(SELECT 
        player_id,
        Date_ADD(min(event_date),INTERVAL 1 DAY)
	FROM Activity
	GROUP BY player_id);