SQL——计算次日留存率

问题:

1. 计算用户是否是次留用户
2. 计算每日次日留存率
   （本文章中的次日留存默认只针对新增用户）

原数据表：user_login_table表

表字段：用户、登陆日期

sql查询：

1. 计算用户是否是次留用户

select t1.user_name,max(case when datediff(day,date(newdate),date(logindate)) = 1 then 1 else 0 end) as 是否次留用户
from 
(
	select user_name,min(logindate) newdate
	from user_login_table
	group by user_name
) t1 join user_login_table t2 on t1.user_name = t2.user_name 
group by t1.user_name

查询结果：

2. 计算每日次日留存率

select t1.logindate 日期,count(distinct case when datediff(day,date(newdate),date(t3.logindate)) = 1 then t3.user_name else null end) 次日留存用户数,count(distinct t2.user_name) 新增用户数,
bi_division(次日留存用户数*100,新增用户数,2) || '%' "次日留存率"
from 
(
	select distinct logindate 
	from user_login_table
) t1 left join 
(
	select user_name,min(logindate) newdate
	from user_login_table
	group by user_name
) t2 on t2.newdate = t1.logindate 
left join user_login_table t3 on t2.user_name = t3.user_name 
group by t1.logindate

查询结果：

如果以上sql中t1、t2、t3表之间任意一处用join而非left join（体现join与left join区别），读者可亲自尝试，查询结果如下：