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

第一种解法:

SELECT
  COUNT(distinct q2.device_id, q2.date) / count(DISTINCT q1.device_id, q1.date) as avg_ret
from
  question_practice_detail as q1
  left join question_practice_detail as q2 on q1.device_id = q2.device_id
  and DATEDIFF(q2.date, q1.date) = 1

第二种解法:

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