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

select count(n_c_date)/count(c_date) as avg_ret
from (
select t1.c_date c_date,t1.device_id,t2.c_date n_c_date
from 
(select date(q.`date`) c_date,q.device_id 
from question_practice_detail q group by c_date ,device_id)t1
left join 
(select date(q.`date`) c_date,q.device_id 
from question_practice_detail q group by c_date ,device_id )t2
on t1.device_id = t2.device_id and date_add(t1.c_date,interval 1 day) = t2.c_date 
) t3