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

distinct可用于选择不同的元组

select count(qp2.device_id)/count(qp1.device_id)
# count(*)
# sum(
#     case when qp2.id is null then 0
#          else 1
#     end
# ) as 'avg_ret'#/count(*)
from 
    (select DISTINCT device_id, date from question_practice_detail) as qp1
 left join 
    (select DISTINCT device_id, date from question_practice_detail) as qp2
on qp1.device_id=qp2.device_id and qp2.date=qp1.date+1
# 

# select *
# from question_practice_detail qp1 left join question_practice_detail qp2 on qp2.date=qp1.date+1
# where qp2.id is null