題解 | #?？兔總€人最近的登錄日期(三)#
?？兔總€人最近的登錄日期(三)
http://www.fangfengwang8.cn/practice/16d41af206cd4066a06a3a0aa585ad3d
1.獲得總用戶數(shù)
select count(distinct user_id) from login
2.獲得每位用戶第二次登錄的理論時間next_date，t1表
select user_id, DATE_ADD(min(date),INTERVAL 1 DAY) next_date
from login
group by user_id;t1
3.用user_id連接原表和t1表，計數(shù)原表中date中存在next_date時的user_id的個數(shù)
select count(distinct t1.user_id)
from login l1
join (select user_id, DATE_ADD(min(date),INTERVAL 1 DAY) next_date
from login
group by user_id )t1
on l1.user_id=t1.user_id
where l1.date=t1.next_date
4.將第3步的值除以第1步的值，結(jié)果保留三位小數(shù)
select round(count(distinct t1.user_id)/(select count(distinct user_id) from login),3)
from login l1
join (select user_id, DATE_ADD(min(date),INTERVAL 1 DAY) next_date
from login
group by user_id )t1
on l1.user_id=t1.user_id
where l1.date=t1.next_date
