题解 | #计算用户的平均次日留存率#
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
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
