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

select sum(back)/count(*) as avg_ret from 
(select p1.device_id,p1.date,case when sum(case when datediff(p1.date,p2.date)=1 then 1 else 0 end)!=0 then 1 else 0 end as back from
question_practice_detail p1
left join question_practice_detail p2
on p1.device_id=p2.device_id
group by p1.device_id,p1.date) p3