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

select avg(if(datediff(date,lag_date)=1,1,0)) as avg_ret
from (
     select device_id,date,lag(date) over (partition by device_id order by date) lag_date
from (
     select distinct device_id,date from question_practice_detail
         )t1
         )t2;

lag lead