题解 | 牛客每个人最近的登录日期(五)

select distinct ll.date,ifnull(p,0)from login ll
left join 
(select b.date,round(a.t/b.s,3) p from 
(select date,count(*) t from login where (user_id,date)in
(select user_id,date_add(min(date),interval 1 day) date from login group by user_id)
group by date) a 
join
(select e.date,count(*) s from 
(select user_id,min(date) date from login group by user_id) e
group by e.date) b
on a.date = date_add(b.date,interval 1 day)) oo
on ll.date = oo.date
order by ll.date

我的方法较复杂，但是也求出来了，先求出每日新增用户个数，然后求的是新增用户日期第二天的用户数，最后关联全部日期，做比例，ifnull判空