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

 # SELECT
#     COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) AS avg_retention
# FROM
#     question_practice_detail AS q1
#     LEFT JOIN question_practice_detail AS q2 ON q1.device_id = q2.device_id
#     AND DATEDIFF(q2.date, q1.date) = 1;
select
    round(avg(if(datediff(date2,date)=1,1,0)),4) as avg_ret
from
    (
        select
            date,
            lead(date) over (
                partition by
                    device_id
                order by
                    date
            ) as date2
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) as t1
    ) as t2