题解 | #计算用户的平均次日留存率#

select
sum(case when datediff(t1.m,t1.date) =1 then 1 else 0 end)
/count( device_id)
from(
select
t.device_id,
t.date,
lead(t.date,1)over(partition by t.device_id order by t.date) as m
from(
select
distinct device_id,
date
from question_practice_detail
) as t 
) as t1
难点1 数据有重复的先要去重在提取数据
难点2 不能用留存率方式来做，因为，连续登录也算次数