每日sql -用户两天留存率

用户两天留存率

数据列：
id device_id quest_id result date

select count(date2) / count(date1) as avg_ret
from
(select distinct d1.device_id, d1.date as date1, d2.date as date2
from question_practice_detail d1
left join(
select distinct device_id, date
from question_practice_detail) d2
on d1.device_id = d2.device_id
and date_add(d1.date, interval 1 days) = d2.date
)a

select avg(if(datediff(date2,date1)= 1,1,0)) as avg_ret
from(
select distinct device_id,date as date1,lead(date) over(partition by device_id order by date) as date2
from (select distinct device_id,date from question_practice_detail) b
)a