分开计算 | #计算用户的平均次日留存率#

select
(select count(distinct a.device_id, a.date)
from question_practice_detail a
join question_practice_detail b
on a.device_id = b.device_id and datediff(b.date, a.date) = 1) /
(select count(distinct device_id, date)
from question_practice_detail) avg_ret

分两个表计算用户id、日期去重后的用户数，满足次日留存即日期数相差1，示例结果应为3/10=0.3。