目录
题目描述
思路整理
1. 首次登录日期
2. 第二天登录
3. 计算比率
实现思路
完整代码及解释
题目描述
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
思路整理
1. 首次登录日期
首先，我们需要找到每位玩家的首次登录日期。这可以通过对每个 player_id 按 event_date 进行分组并选择最小的日期来实现。
2. 第二天登录
然后，我们需要找出那些在首次登录之后的第二天也有登录记录的玩家。这可以通过检查每位玩家的登录记录，看看是否存在一个登录日期正好是首次登录日期后一天的记录。
3. 计算比率
最后，我们计算符合第二天登录条件的玩家数量，然后除以总的独特玩家数量，得到所需的比率。
实现思路
为了实现上述步骤，我们可以使用以下策略：
- 子查询/CTE：首先通过一个子查询或CTE（公共表表达式）来确定每位玩家的首次登录日期。
- 关联查询：然后将原始
Activity表与上述结果进行关联，以找出那些在首次登录后第二天有登录记录的玩家。 - 聚合和计算：通过聚合函数计算符合条件的玩家数量，再除以总的独特玩家数量，最后使用
ROUND函数格式化结果。
完整代码及解释
WITH FirstLogin AS (
SELECT player_id, MIN(event_date) AS first_login_date
FROM Activity
GROUP BY player_id
)
SELECT ROUND(
COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity),
2
) AS fraction
FROM Activity a
JOIN FirstLogin fl ON a.player_id = fl.player_id
WHERE a.event_date = DATE_ADD(fl.first_login_date, INTERVAL 1 DAY);
通过
