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

select count(*) / (select count(*)
                   from (select device_id, date, count(*)
                         from question_practice_detail
                         group by device_id, date) as date1) as avg_ret
from (select device_id, date
      from question_practice_detail
      group by device_id, date) as qpd1,
     (select device_id, date
      from question_practice_detail
      group by device_id, date) as qpd2
where qpd1.device_id = qpd2.device_id
  and date_add(qpd1.date, interval 1 day) = qpd2.date;