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

# 这个理论上知道怎么弄，但是转换到MySQL命令上就毫无头绪
# 看讨论区的解题情况


# select 
#     round(count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date),4) as avg_ret
# from question_practice_detail q1 
# left outer join question_practice_detail q2 
# on DATEDIFF(q1.date,q2.date)=1 and q1.device_id = q2.device_id;


# select avg(if(q2.device_id is not null,1,0)) as avg_ret
# from
#     (
#         select distinct device_id, date
#         from question_practice_detail
#     )q1
#     left join
#     (
#         select distinct device_id, date_sub(date,interval 1 day) as date
#         from question_practice_detail
#     )q2
#     on 
#         q1.device_id = q2.device_id and q1.date = q2.date;


SELECT 
    ROUND(AVG(IF(q2.date IS NOT NULL, 1, 0)), 4) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) q1
LEFT JOIN 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) q2
ON 
    q1.device_id = q2.device_id
    AND q2.date = DATE_ADD(q1.date, INTERVAL 1 DAY);