解题思路:
首先想到要得到每个用户最先登录的日期“MIN(event_date)”然后加1得到第二天也登录了的日期,接着去查询表中存在多少加1后日期的记录,除以所有用户得到概率。
SELECT ROUND(COUNT(*)/
(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)
下面我们来看看力扣官方给出的答案:
首先,求出所有用户首次登录的第二天的时间。方法是查询出 Activity 表中每个用户的第一天时间,并加上 1.
select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
from Activity
group by player_id
将此表命名为 Expected。随后我们要从 Activity 表中查询 event_date 与 Expected.sencond_date 重叠的部分,注意此判定要限定在用户相同的前提下。这部分用户即为在首次登录后第二天也登录了的用户:
select Activity.player_id as player_id
from (
select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
from Activity
group by player_id
) as Expected, Activity
where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id
将此表命名为 Result。随后我们只需要得到 Result 表中用户的数量,以及 Activity 表中用户的数量,相除并保留两位小数即可。
select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction
将以上三步组合起来,得最终的查询:
select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction
from (
select Activity.player_id as player_id
from (
select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
from Activity
group by player_id
) as Expected, Activity
where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id
) as Result, Activity
作者:力扣官方题解
链接:https://leetcode.cn/problems/game-play-analysis-iv/solutions/2477611/you-xi-wan-fa-fen-xi-iv-by-leetcode-solu-h5fj/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。