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

select 
round(count(d2)*1.0/count(d1)*1.0,4)
from
(
select distinct device_id,date d1
from question_practice_detail 
)b
left outer join
(
 select distinct device_id, date d2
from
question_practice_detail 
where (device_id,date) in (
  select distinct device_id,date_add(date,interval 1 day)
from question_practice_detail   
)
)c
on b.device_id=c.device_id and date_add(b.d1,interval 1 day)=c.d2