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

select distinct
    (select count(distinct a.device_id,a.date) 
        from 
                (select distinct device_id,date_add(date,INTERVAL 1 day) as date 
                    from question_practice_detail as a 
                         order by date_add(date,INTERVAL 1 day) asc) as a #假定隔天过来刷题的数据 为与当天刷题数据进行对比并赋别名a
                right join #进行连接为查询实际隔天刷题人数
                (select device_id,date 
                    from question_practice_detail) as q 
                on a.device_id = q.device_id and a.date = q.date #与原表当天刷题人数进行对比 重复数据则为第二天仍来刷题人员
    )#查询出当天来刷题并且隔天也来刷题的总人数
    / #除号
    (select count(distinct device_id,date)
        from question_practice_detail 
            order by date asc
    ) as avg_ret #查询所有日期过来刷题的总人数
from question_practice_detail;