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