SQL留存问题

 1 -- 三日留存率
 2 
 3 select substr(a.active_time,1,10) actday,count(a.userid) act,count(b.userid) lct,
 4 concat(round(count(b.userid)/ count(a.userid),2)*100,'%') lclv
 5 from active_log a left join active_log b on a.userid = b.userid
 6 and datediff(b.active_time,a.active_time) = 3
 7 group by substr(a.active_time,1,10)
 8 
 9  
10 
11 -- 3/5/7留存
12 select substr(a.active_time,1,10) actday,count(distinct a.userid) act
13 ,count(distinct case when datediff(b.active_time,a.active_time) = 3 then b.userid end) 3_lct
14 ,count(distinct case when datediff(b.active_time,a.active_time) = 5 then b.userid end) 5_lct
15 ,count(distinct case when datediff(b.active_time,a.active_time) = 7 then b.userid end) 7_lct
16 from active_log a left join active_log b on a.userid = b.userid
17 group by substr(a.active_time,1,10)
posted on 2025-06-23 12:37 北京的小乔阅读(7) 评论(0) 收藏举报