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