LeetCode刷题---游戏玩法分析 IV

1.首先查询出每个用户首次登录的第二天，并将其创建为临时表，命名为Expected

(select player_id,Date_add(min(event_date),Interval 1 day) as second_date
from activity
group by player_id) as Expected

这里使用了函数DATE_ADD，详细用法可参考：DATE_ADD函数详解

2.将Expected表和原始表activity进行关联，求出首次登录后第二天又登录的player_id，将其创建为临时表，命名为result

(
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 Expected.second_date=activity.event_date and Expected.player_id=activity.player_id) as result

3.将从Result表中查询出来的用户个数除以原表activity中用户的个数，结果保留两位小数

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 Expected.second_date=activity.event_date and Expected.player_id=activity.player_id) as result,activity

LeetCode官方题解