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

select sum(if(b.jiangetianshu=1,1,0))/count(distinct b.device_id,b.date) as avg_ret
from
(select a.*,DATEDIFF(a.houyitian,a.date)jiangetianshu
from
(select device_id,date,ROW_NUMBER() over(partition by device_id
order by date asc) as r,lead(date,1) over(partition by device_id
order by date asc) houyitian
from question_practice_detail
order by device_id,r asc)a)b