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

select
count(b.device_id) / count(a.device_id) avg_ret
from
    (
        select
            distinct q1.device_id device_id,
            q1.date qdate
        from
            question_practice_detail q1
    ) a
    left join (
        select
            distinct q2.device_id device_id,
            q2.date qdate
        from
            question_practice_detail q2
    ) b on a.device_id = b.device_id
    and b.qdate = date_add(a.qdate, interval 1 day);