面试题模拟 游戏玩法分析 IV

题目

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

Orancle代码 

/* Write your PL/SQL query statement below */
select round(avg (case when a.event_date is not NULL then 1 else 0 end), 2) as fraction
from (
    select player_id, min(event_date) as login
    from Activity
    group by player_id
) p
left join Activity a on p.player_id = a.player_id and trunc(a.event_date) = trunc(p.login) + 1;

解析

  对于每个玩家，首先通过子查询从Activity表中获取他们的首次登录日期（login），也就是min(event_date)。

  然后，查询通过左连接（LEFT JOIN）将每个玩家的首次登录日期与Activity表中的其他记录进行比较，以检查是否存在一个记录，其event_date正好是玩家首次登录日期的下一天。

  对于每个玩家，如果存在这样的记录（即玩家在首次登录后的第二天也登录了），则CASE语句会为该玩家分配一个1，否则分配一个0。这样，每个玩家都会被标记为是否连续登录。

  AVG函数会计算所有玩家的这些1和0值的平均值，得到一个比例，表示连续登录的玩家占总玩家数的比例。

  最后，ROUND函数将这个比例四舍五入到小数点后两位。