次留存率的计算

可可宋

已于 2022-05-29 23:02:13 修改

阅读量407

点赞数

文章标签： sql

于 2022-04-18 18:54:10 首次发布

原文链接：https://blog.csdn.net/weixin_39530695/article/details/118971013

版权

##1
select
 log_day '日期',
 count( id_d0 ) '新增数量',
 count( id_d2 ) / count( id_d0 ) '次日留存',
 count( id_d3 ) / count( id_d0 ) '3日留存',
 count( id_d7 ) / count( id_d0 ) '7日留存' 
from
 (
 select distinct
  log_day,
  a.id_d0,
  b.id as id_d2,
  c.id as id_d3,
  d.id as id_d7
 from
  ( select distinct 
   date(dt) as log_day,id as id_d0 
   from dau 
   where date(dt)  between '2021-06-01'  and  '2021-06-30' 
   group by id 
  ) a
  left join  dau b on datediff(date(b.dt), a.log_day ) = 1 
  and a.id_d0 = b.id
  left join dau c on datediff(date(c.dt), a.log_day ) = 2 
  and a.id_d0 = c.id 
  left join dau d on datediff(date(d.dt), a.log_day ) = 6 
  and a.id_d0 = d.id 
 ) 
group by log_day;