题解 | #计算用户的平均次日留存率#
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
select
round(count(date2)/count(date1), 4) as avg_ret
from
(select distinct a.device_id,
a.date as date1,
b.date as date2
from
question_practice_detail a
left join question_practice_detail b
on a.device_id = b.device_id
and datediff(b.date,a.date) = 1
) c
DATEDIFF 是一个日期函数，用于计算两个日期之间的差距，并以指定的时间单位返回结果datediff(b.date,a.date) = 1
或者DATE_ADD(q1.date, interval 1 day)