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

/*计算留存率*/
select   count(new_date2)/count(new_date1) as avg_ret
from(
    select 
        distinct qpd.device_id as new_dev_id_1,
        qpd.date as new_date1
    from question_practice_detail as qpd
) as day1
left join(
    select 
        distinct qpd.device_id as new_dev_id_2,
        date_add(qpd.date,interval 1 day) as new_date2
    from question_practice_detail as qpd 
) as day2 
on day1.new_dev_id_1=day2.new_dev_id_2 and day2.new_date2=day1.new_date1;