题解 | #计算用户的平均次日留存率#
计算用户的平均次日留存率
http://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
关键点：
-
日期计算函数 DATEDIFF 计算两个日期变量的差值。
-
逻辑：自join。
select sum(if(is_ret = 1, 1, 0)) / count(1)
from (
select distinct a.device_id, a.date
, if(b.device_id is not null, 1, 0) as is_ret
from question_practice_detail a
left join (
select distinct device_id, date
from question_practice_detail
) b on a.device_id = b.device_id
and DATEDIFF(b.date, a.date) = 1
)a
