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

1. 先把第一天和第二天的数据表进行左连接，连接条件：id相同，日期间隔1天
2. 若第二天日期数据不为NULL，则该用户满足“第一天刷题且第二天也来刷题”，因此直接count（第二天）/count（第一天）即可。

SELECT DISTINCT count(newdf.date2)/count(newdf.date1)
From
(
select DISTINCT df1.device_id as id,df1.date as date1,df2.date as date2
from question_practice_detail as df1
LEFT JOIN
(select device_id,date
from question_practice_detail) as df2
on df1.device_id = df2.device_id AND
day(df1.date)+1 = day(df2.date) AND month(df1.date) = month(df2.date)
)   as newdf