SQL面试必考——计算留存率"/>
SQL面试必考——计算留存率
leetcode 1097. 游戏玩法分析 V
计算第一天的留存率
法一:窗口函数
- 找到安装游戏的时间,及每个玩家第一次登陆的时间,通过min()+窗口函数,可以既找到最小值,又保留原来的值。(若使用聚合函数group by则只能保留一组数)
select player_id,event_date,min(event_date) over(partition by player_id) install_dt
from Activity;
- 计算第一天的登陆玩家人数,和第二天仍然登陆的人数。用group by分组计算。
select distinct install_dt,count(distinct player_id) installs,round(sum(if datediff(event_date,install_date)=1,1,0)/count(distinct player_id),2) Day1_retention
from
(select player_id,event_date,min(event_date) over(partition by player_id) install_dt
from Activity) t
group by install_dt;
法二:自连接
- 计算第一天登陆时间
- 通过左连接能够得到第一天登陆的id以及第二天是否继续登陆的信息,如果第二天没有登陆,则为null。count(*)会将null算入。
select distinct install_dt,count(*) installs,round(count(t2.event_date)/count(*),2) Day1_retention
from
(select player_id,min(event_date) install_dt
from Activity
group by player_id) t1
left join Activity t2
on t1.player_id = t2.player_id
and datediff(t2.event_date,t1.install_dt)=1
group by install_dt;
左连接得到的结果如下
t1.player_id | t1.install_dt | t2.player_id | t2.event_date |
---|---|---|---|
1 | 2016-03-01 | 1 | 2016-03-02 |
2 | 2017-06-25 | null | null |
3 | 2016-03-01 | null | null |
更多推荐
SQL面试必考——计算留存率
发布评论