1097. 游戏玩法分析 V

SQL架构

Activity 活动记录表

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

我们将玩家的安装日期定义为该玩家的第一个登录日。

我们还将某个日期 X 的第 1 天保留时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。

编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间。

查询结果格式如下所示：

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

leetcode 数据库题目全部题解

解法一
先找到每个玩家的安装日期。 安装日期 定义为 每个玩家 第一次登录的日期。

对玩家分组，求每组最小的日期，即为登录日期。

结果命名为表A。

(
SELECT player_id,MIN(event_date) AS `install_date`
FROM Activity
GROUP BY player_id
) AS A
依次统计安装日期的总人数和安装日期第二天的总人数。

连接表A和活动表，再按登录日期分组，统计每组的总人数，以及第二天的总人数。

注意：因存在玩家第二天没有登录，需用left join。连接条件同一个玩家第二天又登录。

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
那么，1天后的留存率=return_cnt / installs。

解法二
思路同解法一。

用表连接法求每个玩家的最小日期，即为安装日期。

SELECT A.player_id,A.event_date
FROM Activity AS A
LEFT JOIN Activity AS B ON (A.player_id = B.player_id AND A.event_date > B.event_date)
WHERE B.event_date IS NULL
不存在比最小的日期更小的日期了。因此用left join。最小日期的B.event_date是不存在的。

再连接Activity表。算出最小日期第二天还登录的玩家。

SELECT A.player_id,A.event_date,C.player_id
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
基于此，对最小日期分组，统计安装数和1日后的留存率。

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

作者：jason-2
链接：https://leetcode-cn.com/problems/game-play-analysis-v/solution/liang-chong-jie-fa-by-jason-2-7/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。