题解 | #牛客每个人最近的登录日期(三)#
牛客每个人最近的登录日期(三)
http://www.nowcoder.com/practice/16d41af206cd4066a06a3a0aa585ad3d
第一步找到所有id并去重，因为是找新户
select count(distinct user_id) from login
第二步找到自连接产生第二日登陆的id， 并去重
select distinct a.user_id
from login a
join login b
on a.user_id=b.user_id
and b.date = date_add(a.date,INTERVAL 1 DAY)
join login b
on a.user_id=b.user_id
and b.date = date_add(a.date,INTERVAL 1 DAY)
第三步合并找出比例
select round(count(distinct a.user_id)/(select count(distinct user_id) from login), 3) p
from login a
join login b
on a.user_id=b.user_id
and b.date = date_add(a.date,INTERVAL 1 DAY);
from login a
join login b
on a.user_id=b.user_id
and b.date = date_add(a.date,INTERVAL 1 DAY);
