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

with temp as (
select distinct t1.device_id,t1.date,(case when t2.date then 1 else 0 end) as mark
from question_practice_detail t1 left join question_practice_detail t2 on t1.device_id = t2.device_id and t2.date = t1.date + interval 1 day
)
select round(sum(mark)/count(*),4) as avg_ret
from temp

表的自连接：连接条件是设备号相同，且t2表的日期是t1表的日期+1天。采用case when进行条件判断并形成一列标签行，如果第二天还在则标记1不在则标记0。