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

with table1 as(
    select distinct device_id, date    /* 去重 只保留当天刷题的记录*/
    from question_practice_detail
),
table2 as(
    select distinct device_id, date_add(date,interval 1 day) as next_day  /*去重 构造next day 时间表*/
    from question_practice_detail
)


select count(table2.device_id) / count(table1.device_id) 
from table1 left join  table2 on table1.device_id = table2.device_id 
and table1.date = table2.next_day