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

select
    count(q2.device_id) / count(q1.device_id)
from
    (
        select distinct
            device_id,
            date
        from
            question_practice_detail
    ) q1
    left join (
        select distinct
            device_id,
            date
        from
            question_practice_detail
    ) q2 on q1.device_id = q2.device_id
and
    q1.date = date_sub(q2.date, interval 1 day)