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

select count(m1.device_id)
/
(select count(m.date) from (select distinct y.date,y.device_id,y.question_id,y.result from question_practice_detail y) m)
from
(select distinct a.device_id, a.date, c.date as 'cdate'
from question_practice_detail a
left join 
(select b.device_id, b.date #date_sub(b.date,interval 1 day) as cdate
 from question_practice_detail b) c
on a.device_id = c.device_id 
where
(convert(substring_index(a.date,'-',-1),SIGNED) 
 - convert(substring_index(c.date,'-',-1),SIGNED)) = 1 and
month(a.date) = month(c.date)) m1