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

select
(
select
count(distinct qpd1.device_id, qpd1.date)
from 
question_practice_detail as qpd1
inner join
question_practice_detail as qpd2
on date_format(qpd1.date, "%y%m") = date_format(qpd2.date, "%y%m")
and date_format(qpd1.date,"%d") = date_format(qpd2.date, "%d")+1
and qpd1.device_id=qpd2.device_id
)
/
(
select count(distinct device_id, date) from question_practice_detail
)
as avg_ret

这是我的解法，有无不足请大神指教

我先用distinct去重过滤做到 每个人每天答题 一条数据行；sql中上面的找到对应每个人隔天还会答题的数据行，下面的计数所有每个人每天答题的数据行。