次日留存率

select sum(case when b.device_id is null then 0 else 1 end)/count(if(a.device_id is null,0,1)) as avg_ret
from 
(select distinct device_id, date from question_practice_detail) as a
left outer join 
(select distinct device_id, date 
from question_practice_detail
where (device_id,date) in 
    (select device_id, date_add(date,interval 1 day) from question_practice_detail )) as b
on a.device_id= b.device_id
and date_add(a.date,interval 1 day) = b.date;

次日留存率的题目总是要用到两个关键语句：

1. date_add(@dt,interval 1 day)日期增加一天

2. where (user_id, date) in (select user_id, date_add(@dt,interval 1 day) from table) 两列同时相同