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

/*
前一天刷题，第二天也来刷题的人/所有来的用户
次日留存
d1/d0
*/
-- 不应该和连续登录问题混起来：连续登录3天只能算1次，但是是2次连续登录
/*为什么是3/10而不是2/4*/

with a as (
select
p1.device_id as device_id_1,
p1.date as date_1,
p2.device_id as device_id_2,
p2.date as date_2
from question_practice_detail p1 
left join question_practice_detail p2
on p1.device_id = p2.device_id
and p2.date = date_sub(p1.date,interval 1 day)
)
select
round(count(distinct device_id_2,date_2)/count(distinct device_id_1,date_1),4) as avg_ret
from a