新用户N日留存率 和 用户N日留存率

最近在复习SQL，这个经典题目拿出来写一下加深记忆。
新用户N日留存率
就用牛客网的图拉过来做一下说明
对于新用户，我们要找到的就是ta登录的第一天（min date）和第N天（DATE_SUB(min date INTERVAL N DAY）。
对于留存率，比较常见的方法是left join

所以这里我们用包含min dated的表作为A表，用完整的包含intime outtime(用union连接)的表作为B表

重点在于

A LEFT JOIN B ON A KEY = B KEY AND A.MIN DATE = B.DT-N

这样A的uid全部是新用户，B的uid就是当第N天依旧登录的用户

select a.dt,
       round(count(b.uid)/ count(a.uid),2) as uv_left_rate
from (select uid
      ,min(date(in_time)) dt
      from tb_user_log
      group by uid) as a
left join (select uid , date(in_time) dt
           from tb_user_log
           union
           select uid , date(out_time)
           from tb_user_log) as b 
on a.uid=b.uid
and a.dt=date_sub(b.dt,INTERVAL 1 day)
where date_format(a.dt,"%Y-%m")='2021-11'
group by a.dt

或者不使用left join
下面这个方法重点理解的地方在于
找到uid,dt
同时满足
dt 在 min(dt)里 且 在dt-N里面
与前者异曲同工

with a as
(SELECT uid,DATE(in_time) dt
FROM tb_user_log
UNION
SELECT uid,DATE(out_time) dt
FROM tb_user_log) 


SELECT * 
FROM(SELECT dt,
ROUND(SUM(IF((uid,dt) in (SELECT uid,min(dt) FROM a GROUP BY uid) 
             and (uid,dt) in (SELECT uid,DATE_SUB(dt,INTERVAL 1 DAY) from a),1,0))/SUM(IF((uid,dt) in (SELECT uid,min(dt) FROM a GROUP BY uid),1,0)),2) uv_left_rate
FROM a
WHERE DATE_FORMAT(dt,"%Y-%m") = "2021-11"
GROUP BY dt) b
WHERE uv_left_rate IS NOT NULL

用户N日留存率
和新用户唯一不同的地方在于，我们的A表不再是min date 而是包含全部date（注意去重）
B表为了灵活性 join条件可以写a.date < b.date，这样可以算集中n日留存率
当然也可以写date sub来计算固定的N日留存率

灵活的方式写的话，就会用到case when datediff = n then 1 else 0来计算，总数依旧是a.uid

作者：大只弱鱼
链接：https://zhuanlan.zhihu.com/p/356824116
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

select
a.date as date
, sum((case when datediff(b.date,a.date)= 1 then 1 else 0))/count(a.uid) as '次日留存率'
, sum((case when datediff(b.date,a.date)= 3 then 1 else 0))/count(a.uid) as '三日留存率'
, sum((case when datediff(b.date,a.date)= 7 then 1 else 0))/count(a.uid) as '七日留存率'
from
   （
   select uid
      ,substr(datetime,0,10) as date
   from 
       table
   group by uid 
           ,substr(datetime,0,10)
）a
   left join 
   (
   select uid
      ,substr(datetime,0,10) as date
   from 
       table
group by uid 
        ,substr(datetime,0,10)
)b on a.uid=b.uid and a.date < b.date ---加上了第二个条件可以将join后的表记录数减少一半
group by a.date