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

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