SQL面试必考——计算留存率

编程知识行业动态更新时间:2024-06-13 00:22:21

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;