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

关键点：

• 日期计算函数 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