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

# 知识点：left join自关联  date_sub 

select 
count(b.device_id)/count(a.device_id) as avg_ret
# avg(if(b.device_id is not null,1,0)) as avg_ret
from 
(select 
device_id,
date
from question_practice_detail
group by device_id,date
)a
left join 
(select 
device_id,
date
from question_practice_detail
group by device_id,date
)b
on a.device_id=b.device_id and a.date=date_sub(b.date,interval 1 day)
;