SQL、次日留存率（学习记录）

解法一：

SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1

表自连接使用DATEDIFF()计算日期相差一天的记录。

解法二：

select count(t2.date)/count(t1.date) from

    (select distinct device_id,date from question_practice_detail) t1

    left join

    (select distinct device_id,date from question_practice_detail) t2

    on t1.device_id = t2.device_id

    and t2.date = date_add(t1.date,interval 1 day)

使用子查询，然后用date_add()函数